COUNTIFS Formula with Multiple Criteria
Hi,
I'm trying to figure out a formula to count volunteer hours (Monthly Meeting Hours) by a certain location (Organization Location, "Fairmont Area") for specified timeframe (Quarter, "Qtr 1"). I can get it to work without adding the hours in my dashboard but when I try to add hours get a error . Can you help?
=COUNTIFS(Quarter:Quarter, OR(@cell = "Qtr 1", @cell = "Qtr 2", @cell = "Qtr 3"), [Organization Location]:[Organization Location], "Fairmont Area")
When I try the below in dashboard, I get #INCORRECT ARGUMENT
=COUNTIFS({SWMN Leadership Board Representation Range 1}, OR(@cell = "Qtr 1", @cell = "Qtr 2", @cell = "Qtr 3"), {SWMN MCHS Employee Board Members Range 2}, {SWMN MCHS Employee Board Members Range 1}, "Fairmont Area")
Best Answers
-
Hi @JSammon
Yes, you'll want to use SUMIFS in this instance! And yes, that means this range should be the first thing listed:
=SUMIFS({SWMN MCHS Employee Board Members Range 2}, {SWMN Leadership Board Representation Range 1}, OR(@cell = "Qtr 1", @cell = "Qtr 2", @cell = "Qtr 3"), {SWMN MCHS Employee Board Members Range 1}, "Fairmont Area")
Let us know if this works for you.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @JSammon
It looks like you're referencing one of the columns incorrectly... you only need [square brackets] if there's a space or a number in the column name.
Try changing
[Quarter:Quarter]
to be
Quarter:Quarter
=SUMIFS([Monthly Volunteer Hours]:[Monthly Volunteer Hours], Quarter:Quarter, OR(@cell = "Qtr 1", @cell = "Qtr 2", @cell = "Qtr 3"), [Organization Location]:[Organization Location], "Mankato/North Mankato/St Peter Area")
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
You are missing the criteria for SWMN MCHS Employee Board Members Range 2
-
I didn't add criteria because I want to add hrs in this range, should I really be using a SUMIFS formula and than does this range need to be listed first?
-
Hi @JSammon
Yes, you'll want to use SUMIFS in this instance! And yes, that means this range should be the first thing listed:
=SUMIFS({SWMN MCHS Employee Board Members Range 2}, {SWMN Leadership Board Representation Range 1}, OR(@cell = "Qtr 1", @cell = "Qtr 2", @cell = "Qtr 3"), {SWMN MCHS Employee Board Members Range 1}, "Fairmont Area")
Let us know if this works for you.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you so much, Yes this works for the dashboard. I like to cross-reference using the Summary Sheet but can't get the formula to work.
In the summary sheet, what am I missing? I get #UNPARSEABLE for this for the same formula
=SUMIFS([Monthly Volunteer Hours]:[Monthly Volunteer Hours], [Quarter:Quarter], OR(@cell = "Qtr 1", @cell = "Qtr 2", @cell = "Qtr 3"), [Organization Location]:[Organization Location], "Mankato/North Mankato/St Peter Area")
-
Hi @JSammon
It looks like you're referencing one of the columns incorrectly... you only need [square brackets] if there's a space or a number in the column name.
Try changing
[Quarter:Quarter]
to be
Quarter:Quarter
=SUMIFS([Monthly Volunteer Hours]:[Monthly Volunteer Hours], Quarter:Quarter, OR(@cell = "Qtr 1", @cell = "Qtr 2", @cell = "Qtr 3"), [Organization Location]:[Organization Location], "Mankato/North Mankato/St Peter Area")
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you, that's all it needed!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!