Countifs multiple fields and values

Molly Sudmeier

I"m trying count projects that are in IT Org (column) and are in project states (column) of New, Awaiting Endorsement, Project Analysis, Financial Review, ITSC Approval.

COUNTIFS{IT Org Sheet Reference}, "IT", [{Project State Sheet Reference}, "New"...??How do I do counts of all other project states?

Molly Sudmeier

=COUNTIFS({IT PPM Range 3}, "IT", {IT PPM Range 2}, OR (@cell ="New", @cell = "Awaiting Endorsement", @cell = "Project Analysis", @cell = "Financial Review", @cell = "ITSC Approval", @cell = "Postponed", @cell = "Long Range Planning"))

Paul Newcome

I just copy/pasted from your last one and missed a space between OR and the (.

Let's give this a go...

=COUNTIFS({IT PPM Range 3}, "IT", {IT PPM Range 2}, OR(@cell = "New", @cell = "Awaiting Endorsement", @cell = "Project Analysis", @cell = "Financial Review", @cell = "ITSC Approval", @cell = "Postponed", @cell = "Long Range Planning"))

Paul Newcome

Try something like this...

=COUNTIFS({Other Sheet IT Org column}, "IT", {Other Sheet Project State column}, OR(@cell = "New", @cell = "Awaiting Endorsement", @cell = "Project Analysis", @cell = "Financial Review", @cell = "ITSC Approval"))

Molly Sudmeier

Hmmmmm. this didn't work. I received unparseable. I am truly looking for the values in one column that are IT and the other column meet the project states listed.

=COUNTIFS({IT PPM Range 3}, "IT"{IT PPM Range 2}, OR (@ ="New", @cell = "Awaiting Endorsement", @cell = "Project Analysis", @cell= "Financial Review", @cell = "ITSC Approval", @cell = "Postponed", @cell = Long Range Planning"))

Molly Sudmeier

@Andrée Starå - wondering if you have thoughts too?

Paul Newcome

You missed a comma after "IT".

=COUNTIFS({IT PPM Range 3}, "IT", {IT PPM Range 2}, OR (@ ="New", @cell = "Awaiting Endorsement", @cell = "Project Analysis", @cell= "Financial Review", @cell = "ITSC Approval", @cell = "Postponed", @cell = Long Range Planning"))

Molly Sudmeier

I'm still receiving unparsable. :(

Molly Sudmeier

=COUNTIFS({IT PPM Range 3}, "IT", {IT PPM Range 2}, OR (@cell ="New", @cell = "Awaiting Endorsement", @cell = "Project Analysis", @cell = "Financial Review", @cell = "ITSC Approval", @cell = "Postponed", @cell = "Long Range Planning"))

Paul Newcome

I just copy/pasted from your last one and missed a space between OR and the (.

Let's give this a go...

=COUNTIFS({IT PPM Range 3}, "IT", {IT PPM Range 2}, OR(@cell = "New", @cell = "Awaiting Endorsement", @cell = "Project Analysis", @cell = "Financial Review", @cell = "ITSC Approval", @cell = "Postponed", @cell = "Long Range Planning"))

Molly Sudmeier

That is it! Thank you so much!! Yayayayayay!

Paul Newcome

Great! Happy to help! 👍️

Andrée Starå

@Molly Sudmeier

I saw that Paul answered already!

Glad you got it working!

Let me know if I can help with anything else!

Best, 

Andrée