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

Sheet Example.JPG

Find more posts tagged with

Accepted answers

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

All comments

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:

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

Cheers,

Genevieve

JSammon

Thank you, that's all it needed!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.