I go through several threads of this forum and understand SUMIF/SUMIFS is not implemented in Glide App. Please correct me if I’m wrong or if alternative/workaround are already known.
In my “small market of local foods” ordering’s App, design is as follows:
- Several sheets of Products (from Gsheets), these are entry data containing list of Products with Price and description Details.
- 1 sheet for the list of Clients (key is Email), these are entry data get from User entries (Subscription Form).
- 1 sheet for the list of all Orders (Product and Quantity) : there is 1 line per ordered product (key is Email, stored in Column when coming back from ordering Form)
My concern is the following :
- how to show the Amount (Quantity * Price) + Date&Time of the ordered Product when coming back from Form ? especially knowing several orders can be submitted for the same Product (in that case LAST order should be considered).
One idea could be to use the generated Unique ID (and to store it somewhere, see next point) as filter to find the right ordering line in the Orders sheet, but how can it be fetched by the App ?
Do you see other solutions ?
- how to indicate the Total Amount of all ordered Products for that client (Email key) + Date&Time of the last ordered Product ?
One idea could be to use SUMIF (Amounts column of Orders sheet ; Criteria (Email) ; Emails Column of Orders Sheet) on Gsheets, but how can this function be set/refreshed somewhere without disturbing the orders “1 by 1 line” of logging process ? and how can it be fetch by the App ?
Use of Rollup looks not possible because this function don’t have the possibility to use a filter (Email).
I thank for your reply, it’s an important point for this App, having these 2 information not available on the App may endanger its use/deployment and force to think at alternative to Glide ;-(.
At this stage, solution “out of the App” like Stripe payment solution is to exclude because 1st need is to facilitate the ordering process of food during the quarantine period, payment will be seen later.
Would love roll-up with conditional …sumifs style
If you have a relation column set up, you can use a Rollup column against it. As long as you use proper templates or values to match up to the data you want to Rollup, then it will work. It’s more or less the same as a sumif, but it only works for exact matches. It will not work for greater than or less than operations.
2 Likes
Thanks Jeff 
this sounds good because in my case the condition is exact match (Email). I think use of “Template” is likely missing in my App. Would you please let me knnow where I could find example of this “SUMIF/SUMIFS” look like function ?
Bravo ! Thanks Jeff, I understood the trick after looking at the setting of 2 the parameters “Paid” (by each person) and “Total Expenses” of https://go.glideapps.com/app/expense-splitter-template/data example where the 1st is going through an “Expenses” Relation to make the Rollup and the 2nd via classical Gsheets access.
I implemented it in my App and it works fine, the thing is that the (€) format was lost (I guess characters are transferred), so I made a reformatting after the Rollup using the Math function.
The only remaining problem is the format of the Quantity parameter, if it’s “0” (or /=0) it’s ok, but if empty, it is interpreted as “1” !!! consequence it that RollUp execution takes a huuuuge of time and the result is wrong !!! Do you have any idea to solve it ? I already point out this format problem in an other thread. Best solution I think would be to always store “0” when Quantity entry is empty and only authorize positive values (Quantity is the number of products ordered by the client).
A way that I have been working around these types of issues is using an “if>then>else” column to define my If statements. i.e. if ColA is “PAID” then 1, if ColA is “NEW” then 0, else blank. Then you can create a roll up of the If>then>else column for a sum and a count. Sum would give you total number of PAID orders and count gives a total number of orders.
You can also use a rollup column against a relation column for that specific user. Rollups for a date column gives you the ability to return a “latest” value, which would be the most recent order.
I hope that makes sense and helps with your use case
1 Like
Thanks Jeff, here is how I finally deal with the Quantity format issue.
I add following test before the Math function “Amount=Price*Quantity”:
IF Quantity is empty THEN “0€”, IF Quantity is <= 0 THEN “0€”, ELSE “1€”
(and not “0” or “1” that would be badly interpreted by the Math function).
Anyway on my side you can close this thread.
Mille Merci ! you help me to put in place a key function on my App.
Thank you Ben for your answers, things are now clear to me and I’ll be able to transpose it to my case.
However one point let me quite perplex, that is your 1st answer, what do you mean exactly by “create a roll up of the If>then>else column” ? doing that, values of the If>then>else column will be counted, that is not what is wanted. Could you please be more precise ?
Hi Jean-Philippe,
This use can be modified to work in many different ways. You can create different If>then>else columns with different settings that give the specific roll up you are looking for. In my example above it SUM would give the total number of paid orders while COUNT would give the total number of orders. I didn’t fully understand what you are looking to achieve and don’t know how you have your columns structured so I couldn’t provide an exact solution.
I was hoping my example would spark some thoughts for ways you could use that work around for your specific app use case. I hope this helps to clear up my intentions. 
Hi Ben,
So I understand in your example you have 4 Columns (I don’t know how to do it with less):
- ColumnA doing Rollup (COUNT)
- ColumnB doing Rollup (SUM)
- ColumnC acting as a switch (0/1 or TRUE/FALSE)
- ColumnD giving A or B depending on the result of IF-THEN-ELSE on ColumnC
Is it what you have in mind? If yes, it’s clear to me too, but in my opinion it’s rather an “IF-THEN-ELSE on a switch that gives one or the other Rollup value” than a “Rollup of the IF-THEN-ELSE" as you mentioned. Thanks anyway for your answer.