SUMIF with two or more criteria

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


Best Answer

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    Answer ✓

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

Answers

  • Mike TV
    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
    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
    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
    Hollie Green ✭✭✭✭✭✭
    Answer ✓

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!