Formula's won't work together

Krista Coffman

Hi! I need to get the below formula to capture both project stages but when I add them together it gives me zero which is incorrect. I need the metric to match the report on the dashboard, any help is appreciated!

=COUNTIFS({HR Projects - PMO Range 4}, >=TODAY(1), {HR Projects - PMO Range 4}, <=TODAY((30)), {HR Projects - Confidential}, 0, {Project Stage}, "Complete")

=COUNTIFS({HR Projects - PMO Range 4}, >=TODAY(1), {HR Projects - PMO Range 4}, <=TODAY((30)), {HR Projects - Confidential}, 0, {Project Stage}, "In Progress")

Nic Larsen

Try this:

=COUNTIFS({HR Projects - PMO Range 4}, >=TODAY(1), {HR Projects - PMO Range 4}, <=TODAY((30)), {HR Projects - Confidential}, 0, {Project Stage}, OR(@cell = "In Progress", @cell = "Complete"))

Jeremy C

Krista - A couple of questions for ya.

Do the formulas work correctly on their own?

Are these cross referencing other sheets?

Krista Coffman

Hi Jeremy,

Yes they both work on their own and no they are both pulling from the same sheet.

Nic Larsen

Try this:

=COUNTIFS({HR Projects - PMO Range 4}, >=TODAY(1), {HR Projects - PMO Range 4}, <=TODAY((30)), {HR Projects - Confidential}, 0, {Project Stage}, OR(@cell = "In Progress", @cell = "Complete"))

Krista Coffman

Hi Nic,

It tells me that it is unparseable.

=COUNTIFS({HR Projects - PMO Range 4}, >=TODAY(1), {HR Projects - PMO Range 4}, <=TODAY((30)), {HR Projects - Confidential}, 0, {Project Stage}, OR(@{HR Projects - PMO Range 6} = "In Progress", @{HR Projects - PMO Range 8} = "Complete"))

Because I am pulling all my data from the source sheet it won't let me use the @cell format

Nic Larsen

Hmm did you copy/paste my formula over? The one you posted is very different.

If they only change we are making is at the last criteria to say that you want to count where either of those conditions (In Progress/Complete) exist, then you only need to add one OR at the end as I put above.

Krista Coffman

That worked, thank you!