# SUMIF with two or more criteria

Options
✭✭✭✭

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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 ?

• ✭✭✭✭
Options

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

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

kr Joerg

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!