Countif logic not working

Finops C'ship

Hi Team,

I need a formula that should count the based on Priority High, Medium, Low .

So if the priority is High and Status is Completed it should count the number on closed column

if the priority is High and status is In progress,Hold its should count the number on open column

like wise we have to do for Low and medium.

Below I have prepared the Summary sheet were the count should refect.

and this is the input sheet

Please help in getting the formula.

Thanks

Heather Duff

Hi @Finops C'ship,

First, you will want to make sure your project names listed in the source sheet and the summary sheet match. That way, you'll be able to use the same formula for each row on your summary sheet.

Here are the formulas I came up with:

For the High, Open column:

=countifs({project},[Project]@row,{Status},@cell<>"Completed",{Priority},"High")

For the High, Closed column:

=countifs({project},[Project]@row,{Status},@cell="Completed",{Priority},"High")

Then for the rest of the columns you would change the priority level in bold to Medium or Low, and everything else should stay the same.

{Project} is the named range for the Project column in your source sheet. {Status} is the named range for your Status column in your source sheet. {Priority} is the named range for your Priority column in your source sheet.

Hope this helps! Let me know if it works for you.

Best,

Heather

Heather Duff

@Finops C'ship

I forgot to add the formula for the Totals column:

Totals Open:

=COUNTIFS({Project}, Project@row, {Status}, @cell <> "Completed")

Totals Closed:

=COUNTIFS({Project}, Project@row, {Status}, @cell = "Completed")

You can use a simple SUM formula for the row that totals each column.

Finops C'ship

Hi @Heather D

its showing invalid error

=COUNTIFS({Risk Project -#}, Project@row, {Risk Project -#}, @cell <> "Completed", {Risk Project -#}, "High")

Heather Duff

@Finops C'ship

You'll have to have separate named ranges in your cross-sheet reference. So, you'll have one for the Project column, one for the Status column, and one for the Priority column.