Formula result not shows correctly

Giacky91
Participant I

Hi all,
i have a sheet on excel with 5 rows that, for each rows, sum some value from sheet 1 using some condition. Values are prices
Below an example of the formula i’m using on excel:
=SUMIFS(Expense!D:D;Expense!C:C;“A”;Expense!F:F;“Yes”)
=SUMIFS(Expense!D:D;Expense!C:C;“B”;Expense!F:F;“Yes”)
=SUMIFS(Expense!D:D;Expense!C:C;“C”;Expense!F:F;“Yes”)
=SUMIFS(Expense!D:D;Expense!C:C;“D”;Expense!F:F;“Yes”)
=SUMIFS(Expense!D:D;Expense!C:C;“E”;Expense!F:F;“Yes”)

the last row is the sum of the previous.
On AppSheet the value is not correctly shown. I have the text #VALUE! when i try to show the cell value on a table.
How can i fix this solution?
Thanks in advance!

Solved Solved
0 14 1,115
1 ACCEPTED SOLUTION

Try:

sum(select(Expense[Quantity], and([Hype?] = “Yes” , [Created By] = “A”)))

or to match whomever Who refers to:

sum(select(Expense[Quantity], and([Hype?] = “Yes” , [Created By] = [_thisrow].[Who])))

View solution in original post

14 REPLIES 14

I m not Excel expert, but why you don’t run the same calculation within Appsheet virtual column? I think it should be much easier and run app faster as well.

Giacky91
Participant I

Thank you Tsuji.
How can i create the same calculation within Appsheet virtual column?
Is it possible to sum value from different table?

I suggest you create a super simple table only one column called [ID].
And then you manually add value to this fields. Values are
A
B
C
D
E

Now you have 5 rows.

Then on this table create the virtual column with expression something like this.

Ifs(
[ID]=“A”, sum(select(TargetTableNameYouHaveNumber[TargetNumericField], ANYCONDITIONYOUNEEDTOFILTERROS)),
[ID]=“B”, sum(select(TargetTableNameYouHaveNumber[TargetNumericField], ANYCONDITIONYOUNEEDTOFILTERROS)),
[ID]=“C”, sum(select(TargetTableNameYouHaveNumber[TargetNumericField], ANYCONDITIONYOUNEEDTOFILTERROS)),
[ID]=“D”, sum(select(TargetTableNameYouHaveNumber[TargetNumericField], ANYCONDITIONYOUNEEDTOFILTERROS)),
[ID]=“E”, sum(select(TargetTableNameYouHaveNumber[TargetNumericField], ANYCONDITIONYOUNEEDTOFILTERROS)),
)

You can use switch expression, but the result is the same.

This new table is kinds of summary table where you see the result of calculations, but it always run the calculation dynamically.

This should definitely make app run faster rather than letting excel or spreadsheet do the same jobs.

Giacky91
Participant I

I’m trying with only one if:
IF([Who]=“A”, sum(select(Expense[Quantity], Expense[Hype?] = “Yes”)))

but the result is the error:
Cannot compare List with Text in (Expense[Hype?] = “Yes”)

Try:

IFS([Who]=“A”, sum(select(Expense[Quantity], [Hype?] = “Yes”)))

Giacky91
Participant I

Now i have no error, but the result of the sum is 0.
Is it maybe because [Hype?] is in a different table? Expense in that case.

Unfortunately, if i use Expense[Hype?] = “Yes” i have the error Cannot compare List with Text in (Expense[Hype?] = “Yes”)

Within a SELECT() expression, bare column value references are assumed to refer to columns in the table being searched. So in select(Expense[Quantity], [Hype?] = “Yes”), the table being searched is Expense and the bare column value reference [Hype?] is assumed to mean the Hype? column in the Expense table.

understood. But i still receive 0 as a result.
Also, i need to apply a second filter in Expense table. How can i perform this?

That suggests one or more of the following:

  1. The value of the Who column is not A.

  2. There are no rows in the Expense table with a Hype? column value of Yes.

  3. The Quantity column values of the matching rows are all zero.

  4. The column receiving the result is type Number but the Quantity values are type Decimal and add up to less than 1.0.

One problem at a time.

Here the issue: i have quantity as Price and the result type Price. I changed both in Decimal and now the sum is correct, but i lose the currency.

EDIT: i swithced back to Price and now it’s working! Now i need only to apply a second filter to SELECT() function

Ugh! Not the first time I’ve seen that type of glitch… Glad it’s working now!

Tell me more. You might also find this useful:

Thanks for your help!

I need to sum quantity based on [hype?] = “Yes” but also on a different column value, which is Expense.[Created By]. Something like:

IFS([Who]=“A”, sum(select(Expense[Quantity], [Hype?] = “Yes”, [Created By] = “A”)))

Try:

sum(select(Expense[Quantity], and([Hype?] = “Yes” , [Created By] = “A”)))

or to match whomever Who refers to:

sum(select(Expense[Quantity], and([Hype?] = “Yes” , [Created By] = [_thisrow].[Who])))

It Works!
Thank you so much Steve!

Top Labels in this Space