Countif logic not working
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
Answers
-
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
-
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.
-
Hi @Heather D
its showing invalid error
=COUNTIFS({Risk Project -#}, Project@row, {Risk Project -#}, @cell <> "Completed", {Risk Project -#}, "High")
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!