COUNTIFS in different sheet totaling values from two columns

Sharee Brell

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?

Sharee Brell

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.

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

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.

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

Glad the second one worked!

Cheers,

Genevieve