SUMIF (Collect( yyyy; with control button

Joerg Schmidt

Hi,

could you please look up following formula which is "unparseable"

=SUMIF(COLLECT([VS FF&E]:[VS FF&E];Archiv:Archiv; = active))

"active" is for the control button I use in the column. It is either active or not.

I will only sum the value of all rows that are not active.

Hope you can help

David Tutwiler

I'm not sure what you mean by control button, but if you're checking whether something is on or not (checked or unchecked) then you can typically substitute 1 for on and 0 for off. You could try to replace active with the number 1 (no quotes).

David Tutwiler

SUMIF requires a minimum of 2 parameters. The first is the Range and the second is the Criterion. Your collect statement is currently occupying just the Range field.

Joerg Schmidt

Hi David,

thanks for answering. Nevertheless if I change from Sumif to sum

the formula doesn´t work.

How should I set the formula in order to reach the result

I guess the important factor is the name of the "active" or "not active" field - true?

David Tutwiler

Right, so you're looking for:

=SUMIF(Archiv:Archiv, "active", [VS FF&E]:[VS FF&E])

Where the first parameter is the range you're checking the parameter against, the second parameter is what the condition is, the third parameter is the range to get the data from to sum. So it reads:

"If the Archive column on this row is "active" then get the data from the VS FF&E column and add it to the sum"

Joerg Schmidt

Good morning David,

I changed "comma" into "semicolon". Unfortunately, the result is "0" - it should be more than 1

Any further idea? Is "active" really the wright word?

David Tutwiler

I'm not sure what you mean by control button, but if you're checking whether something is on or not (checked or unchecked) then you can typically substitute 1 for on and 0 for off. You could try to replace active with the number 1 (no quotes).

Joerg Schmidt

Hi David,

now it works. Thank you.

Kind regards Joerg

David Tutwiler

Awesome. Great to hear.