SUMIF with two or more criteria

Jörg Schmidt1

Hi,

I use this formula

=SUMIF({Rechnungserfassung Zahlungsstatus}; "Investition geplant"; {Total (Brutto jährlich)})

1) I need to add another criteria f.e. "Zahlung geplant".

2) What if I have various +criteria and want to exclude another one f.e. "exclude payment"

How do I have to change the formula in order to get the right answers

Hollie Green

If I am understanding correctly you want to get the sumif either Zahlung geplant or Investition geplant occur and still be able to exclude if bezahlt occurs? If there are other criteria you want to add just do another + Sumif with that particular criteria.

If the above statement is true the below formula should work for you

=SUMIF({Rechnungserfassung Zahlungsstatus}; "Investition geplant"; {Total (Brutto jährlich)})+Sumif({Rechnungserfassung Zahlungsstatus}; "Zahlung geplant";{Total (Brutto jährlich)})

Mike TV

@Jörg Schmidt1

You need to use the SUMIFS function instead. Just note that right after SUMIFS you'll be putting the range that needs added. After that, it's the criteria_range followed by the criteria. You can keep adding additional criteria_range followed by it's criteria. As many as you want. So it would look something like this:

=SUMIFS({Total (Brutto jährlich)}; {Rechnungserfassung Zahlungsstatus}; "Investition geplant"; {Rechnungserfassung Zahlungsstatus}; "Zahlung geplant")

Hopefully I'm reading your (German?) correctly.

Jörg Schmidt1

Hi @Mike TV ,

your German transfer is brillant :-)

Your formula seems to fit because I do not get a default.

Nevertheless, I receive a "0,- EUR" although there are amounts € in the rows with "Zahlung geplant" and "Investition geplant - as you can see in the screenshot above.

Any other idea ?

Jörg Schmidt1

@Andrée Starå

Hi Andree, hope you had a good start 2023, too?

Do you have an idea why I receive "0,- EUR" as result ?

kr Joerg

Hollie Green

If I am understanding correctly you want to get the sumif either Zahlung geplant or Investition geplant occur and still be able to exclude if bezahlt occurs? If there are other criteria you want to add just do another + Sumif with that particular criteria.

If the above statement is true the below formula should work for you

=SUMIF({Rechnungserfassung Zahlungsstatus}; "Investition geplant"; {Total (Brutto jährlich)})+Sumif({Rechnungserfassung Zahlungsstatus}; "Zahlung geplant";{Total (Brutto jährlich)})

Jörg Schmidt1

Hi @Hollie Green , you made my day 😀

and again the easy solution was right before my nose - only the other way around

Thank you !! and have a nice day (by the way here it is 10:43 pm)