Calculating 2 criteria from one column up to a specific date, also including a check box reference
I'm trying to calculate data from a status column that has drop down options of 'in progress' and 'not started' and see how many from those 2 criteria return an end date up to the end of June 2024 (working days will be 28 June). Also ensuring that a check box for if it's being reported is checked. I'm using a sheet reference, column 3 is the status, 6 is the date range and 1 is the checkbox for reporting; so my current formula is this:
=COUNTIFS({Project name 3}, "in progress", {Project name Range 3}, "not started", {Project name 6}, <DATE(2024, 6, 28), {Project name 2}, "1"
It's not returned an error message, just a zero - and the amount should actually be 24.
My calculation for all activities that end after June 28 that are reported on works,
=COUNTIFS({Project name Range 6}, >DATE(2024, 6, 28), {Project name Range 2}, "1")
so it's the additional parameters of looking at the status which is stumping me! Appreciate any help, thanks.
Best Answer
-
Hi @Bek T,
please try the following formula:
=COUNTIFS({Project name Status}, "in progress", {Project name End Date}, <=DATE(2024, 6, 28), {Project name Reporting}, 1) + COUNTIFS({Project name Status}, "not started", {Project name End Date}, <=DATE(2024, 6, 28), {Project name Reporting}, 1)
In this formula:
- Replace
{Project name Status}
with the correct reference for your status column (which seems to be{Project name 3}
). - Replace
{Project name End Date}
with the correct reference for your end date column (assumed to be{Project name 6}
based on your description). - Replace
{Project name Reporting}
with the correct reference for your reporting checkbox column (you mentioned{Project name 2}
but ensure this is correct).
This approach sums the counts of "in progress" and "not started" items that also meet the end date and reporting criteria, which seems to align more closely with your requirements.
Please make sure to adjust the column references as per your actual sheet's setup, as it seems there might have been a slight confusion or typo in the references you've provided.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
- Replace
Answers
-
Hi @Bek T,
please try the following formula:
=COUNTIFS({Project name Status}, "in progress", {Project name End Date}, <=DATE(2024, 6, 28), {Project name Reporting}, 1) + COUNTIFS({Project name Status}, "not started", {Project name End Date}, <=DATE(2024, 6, 28), {Project name Reporting}, 1)
In this formula:
- Replace
{Project name Status}
with the correct reference for your status column (which seems to be{Project name 3}
). - Replace
{Project name End Date}
with the correct reference for your end date column (assumed to be{Project name 6}
based on your description). - Replace
{Project name Reporting}
with the correct reference for your reporting checkbox column (you mentioned{Project name 2}
but ensure this is correct).
This approach sums the counts of "in progress" and "not started" items that also meet the end date and reporting criteria, which seems to align more closely with your requirements.
Please make sure to adjust the column references as per your actual sheet's setup, as it seems there might have been a slight confusion or typo in the references you've provided.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
- Replace
-
Hey Bassam, thanks so much for this. It worked!
And apologies for any confusion with the project name fields - I manually changed them in the question box to be more generic, but see where I missed one (brain capacity after trying too many countifs variations at the end of a day!)
Ngā mihi :)
-
You are welcome @Bek T
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!