SUMPROD function

zakigha · 05-27-2020 02:30 PM

Hello Team !
I have two google sheets and sheet 2 is using the SUMPROD function with cells in both sheet 1 and 2.
Appsheet doesn’t seem to be able to get this formula…
Is there any mean to remediate to that issue in APPSHEET ?
Thank you for your help !

gmoothart

Hi @zakigha,
In your spreadsheet, is it only the 0 values that show up in AppSheet as “#VALUE!”? Or is it every value? For example. does AppSheet show the values for dec.-14 and dec.-15 correctly?

I’m wondering if there is some edge case being displayed differently in the two.

zakigha

Unfortunately it shows “#VALUE” everywhere…
I think appsheet cannot handle the sumprod formula between two sheets.
When I copy the formula into the AppFormula it doesnt not recognize the function.

hru

For this, it maybe better to connect your 2nd sheet as read only table.

zakigha

Problem is still pending…

hru

you may want to delete the 2nd table, all its views and re-connect as read only table.

Something like below:

zakigha

I might have exposed my problem the wrong way :
Here are my two sheets

hru

Sorry, but I don’t understand. I can only recommend the above.

zakigha

Thank you

gmoothart

Hi @zakigha,
I’m sorry you’re having problems. The best practice for something like this is to remove formulas from the source spreadsheet and incorporate them into the app definition instead. Can you try that?

zakigha

Hi @gmoothart
Thank you for your answer
Indeed, I tried but it seems that appsheet doesnt recongnize the “sumproduct” function

Steve

That is correct. AppSheet functions and spreadsheet functions are not the same.

I imagine there is no easy way for you to translate your spreadsheet formula to AppSheet. In order for us to help you, though, you will need to explain what your formula is trying to do without assuming we have as deep an understanding of spreadsheet formulas as you have. Your formula is a complete mystery to me.

See also:

zakigha

Hi @Steve

I will try to make it simple

I have a liste of sites for which I am receiving several paiements during the years :

Now, what I want to show is a row serie stack view of this with on the X-axis the month and on the Y-axis the total amount of paiement received during that month like this :
Each color corresponds to one site

I managed to do so by creating a second sheet with the SUMPRODUCT formula :
The sumproduct formula is taking the value of cell on the first column (here is A4=“BOULOGNE”) and look for all the rows corresponding to that value on the identified column (here column “C” of the other sheet “CA TOUT”). Then it takes the value of the cell of the first row above (here C1=dec-15) and look for the row having that condition on the identified column of the other table (here column H) and then return the sum of the cells of the identified column (here is column I) corresponding the rows having both previous conditions.

Now this poses 2 problems :
1-sumproduct is not recognize by appsheet so I created a copy of this last sheet and copy paste the values so that appsheet can recognize it.
2-whenever I am adding a new site and a new payment, I need to manually add the site on the second sheet to generate my chart.

Hope it sounds a bit clearer and that you will be able to help me !

Steve

Based on my interpretation of the above, I believe an equivalent AppSheet expression would roughly:

SUM(
  SELECT(
    CA TOUT[PAID],
    AND(
      ([_THISROW].[SITE] = [SITE]),
      ([_THISROW].[dec-15] = [DATE])
    )
  )
)

This sums the PAID column values in the CA TOUT table only from rows where the SITE column value matches the SITE column value of the sumproduct table and the DATE column value matches the dec-15 column value of the sumproduct table.

A big problem, though, is that the header row of the AppSheet table is not an addressable data value; there is no equivalent to referencing C1 to get dec-15. Which takes us to…

Obviously, having to manually update the spreadsheet every month or when adding a new site is not ideal. And because the table is currently using per-month columns, the app itself will also have to be updated monthly with the new month’s column. The data structuring you’ve done here is intended to support your charting needs, but doesn’t fit well with AppSheet’s expectations. Unfortunately, I don’t have much experience with AppSheet’s charting features, so I can’t offer any direct recommendations for using them or structuring your data suitably.

zakigha

Thank you @Steve ! A solution is better than no solution at all !