Formula result not shows correctly

[Giacky91]

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!

[Koichi_Tsuji]

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]

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

[Koichi_Tsuji]

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]

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”)

[Steve]

Try:

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

[Giacky91]

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”)

[Steve]

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.

[Giacky91]

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?

[Steve]

Giacky91:

i still receive 0 as a result.

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.

Giacky91:

Also, i need to apply a second filter in Expense table. How can i perform this?

One problem at a time.

[Giacky91]

Steve:

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

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

[Steve]

Giacky91:

i swithced back to Price and now it’s working!

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

Giacky91:

Now i need only to apply a second filter to SELECT() function

Tell me more. You might also find this useful:

help.appsheet.com

AND()

Are all true?

[Giacky91]

Steve:

Glad it’s working now!

Thanks for your help!

Steve:

Tell me more.

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”)))

[Steve · 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])))

[Giacky91]

It Works!
Thank you so much Steve!