SUMIF vs. SUMIFS

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

Find more posts tagged with

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Dashboard Adding Widgets Spontaneously
Howdy, I am working on developing a several dashboards for our team, and for whatever reason the widgets I place are multiplying. I have copied/pasted some, but other I have just added I am ending up with dashboards that look like this: When originally I only had the top four items. It has happened on several different…
Project Management with tracking costs
I am a construction project manager and built sheets that include actual costs for individual tasks, departments and overall project. I update the costs cells as invoices come in by manually calculating then entering in new amount. Ex: Plumbing: week 1 $300, week 2 $250, week 3 $150 = $700. I enter $700 in cell. I'm…
Showing "stacked" data / Statuses of items by swimlane
I have multiple items that each have their own statuses and that roll up to "workstreams" or swimlanes. On the dashboard, I would like to show the progress of the items by swimlane without breaking it out into separate widgets for each. This would ideally be a stacked bar where each bar represented the Items and was broken…