COUNTIFS Formula with Multiple Criteria

JSammon

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

Sheet Example.JPG

Genevieve P.

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

Genevieve P.

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

Sameer Karkhanis

You are missing the criteria for SWMN MCHS Employee Board Members Range 2

JSammon

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?

Genevieve P.

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

JSammon

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

Genevieve P.

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

JSammon

Thank you, that's all it needed!