# COUNTIFS in different sheet totaling values from two columns

Options

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?

Options

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. :)

• Employee
edited 11/01/21
Options

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

=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

October 8 - 10, Seattle, WA | Register now

Options

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. :)

• Employee
Options

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