COUNTIFS in different sheet totaling values from two columns

Hey there,

I would appreciate some help. I've tried multiple formulas and can't get it right and hope you can help.

I have a summary sheet which i am referencing a sheet to gather the total active projects for a department. There is a dept column and a status column. I created Dept_Ref where both those columns are selected.

I know that one department only has 1 project in 'on hold' status and I am using this as my first validation.

When I use

=COUNTIFS ({Dept_Ref},"Analytics",{Status_Ref},"On Hold")

I get the correct calculated result = 1

I then tried to add more status values and get the wrong result:

=COUNTIFS ({Dept_Ref},"Analytics",{Status_Ref},"On Hold","Under Development")

This returns a calculated result = 0. It should still be 1.

Can someone help me understand what I have incorrect in the second formula?

Best Answer

  • Sharee Brell
    Sharee Brell ✭✭
    Answer ✓

    Unfortunately, this: =COUNTIFS({Dept_Ref}, "Analytics", {Status_Ref}, "On Hold") + =COUNTIFS({Dept_Ref}, "Analytics",{Status_Ref}, "Under Development")

    returns = Invalid Operation


    The second one though - WORKED!!! Thank you Genevieve. :)

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 11/01/21

    Hi @Sharee Brell

    In your second formula, you're asking it to look for one cell that contains both values, both "On Hold" and "Under Development".

    Try adding together two separate COUNTIF statements instead, like so:

    =COUNTIFS({Dept_Ref}, "Analytics", {Status_Ref}, "On Hold") + COUNTIFS({Dept_Ref}, "Analytics",{Status_Ref}, "Under Development")

    You could also add in an OR statement:

    =COUNTIFS({Dept_Ref}, "Analytics", {Status_Ref}, OR(@cell = "On Hold", @cell = "Under Development"))


    Let me know if either of these worked for you!

    Cheers,

    Genevieve

  • Sharee Brell
    Sharee Brell ✭✭
    Answer ✓

    Unfortunately, this: =COUNTIFS({Dept_Ref}, "Analytics", {Status_Ref}, "On Hold") + =COUNTIFS({Dept_Ref}, "Analytics",{Status_Ref}, "Under Development")

    returns = Invalid Operation


    The second one though - WORKED!!! Thank you Genevieve. :)

  • Genevieve P.
    Genevieve P. Employee Admin

    Oops, my apologies! there was an extra = in my first formula... 🤦‍♀️

    Glad the second one worked!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!