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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭

    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?

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!