SUMIF vs. SUMIFS

Pattyk

I am looking to add the total hotel night stays from multiple labels in a single criteria column. This is my formula, but it results in "0" which is incorrect: 

=SUMIFS(NIGHTS10:NIGHTS38, AFFILIATION10:AFFILIATION38, "Researcher", AFFILIATION10:AFFILIATION38, "RAN", AFFILIATION10:AFFILIATION38, "ECD awardee")

 

NIGHTS:NIGHTS = # of hotel night stays

AFFILIATION:AFFILIATION = location of labels (criteria)

SUMIF or SUMIFS? What am I missing in the formula above if using SUMIFS?

Patty

Andrée Starå

Hi Patty,

Try this.

SUMIFS adds numbers within a range that meet multiple criteria so all criteria needs to be true for the formula to sum.

=SUMIFS(NIGHTS10:NIGHTS38; AFFILIATION10:AFFILIATION38; "Researcher") + SUMIFS(NIGHTS10:NIGHTS38; AFFILIATION10:AFFILIATION38; "Ran") + SUMIFS(NIGHTS10:NIGHTS38; AFFILIATION10:AFFILIATION38; "ECD awardee")    

The same version but with the below changes for your and others convenience.  

=SUMIFS(NIGHTS10:NIGHTS38, AFFILIATION10:AFFILIATION38, "Researcher") + SUMIFS(NIGHTS10:NIGHTS38, AFFILIATION10:AFFILIATION38, "Ran") + SUMIFS(NIGHTS10:NIGHTS38, AFFILIATION10:AFFILIATION38, "ECD awardee")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Pattyk

YES!  WOOHOO!!

thank you, Andree.

Andrée Starå

Great!

Happy to help!

Best,

Andrée