Countifs- if cell blank then count by other cell
Hi
I currently have following formula counting number of projects with status completed in EU and checking base date monthly:
=COUNTIFS({production date}, <=DATE(2023, 1, 31), {base eu kof}, >=DATE(2023, 1, 1), {status}, "Completed", {division}, "EU")
However some projects don't have "production date" they've got another date= "shipment date", in those cases i need to count by that date and cannot figure out the formula for that.
Summing up i need to have formula like that:
IF production date is in place then count by that date, IF production date IS BLANK count by shipment date
Any idea how to write it?
Regards,
Piotr
Best Answer
-
FIY for anyone struggling with similar problem in the future
like always sleep helped and i came up with solution that works:
=COUNTIFS({Production date}, <=DATE(2023, 1, 31), {Production date}, >=DATE(2023, 1, 1), {status}, "Completed", {division}, "EU") + COUNTIFS({Shipment date}, <=DATE(2023, 1, 31), {Shipment date}, >=DATE(2023, 1, 1), {status}, "Completed", {division}, "EU", {Production Date}, <>"")
seems to be doing what i wanted. So basically it's gathering all projects with Production date in place and then adding projects without Production date based on the same conditions but by Shipment date
Answers
-
I would suggest a helper date column in the source sheet that pulls in the date you want to use via an IF statement.
=IF([Production Date]@row <> "", [Production Date]@row, [Shipment Date]@row)
Then reference this date column in your COUNTIFS.
-
I was trying to avoid that as our tracker has a lot columns now and we're trying to trim it up rather then expand. Maybe i'll do it in an additional tracker if nothing else will come to me
-
FIY for anyone struggling with similar problem in the future
like always sleep helped and i came up with solution that works:
=COUNTIFS({Production date}, <=DATE(2023, 1, 31), {Production date}, >=DATE(2023, 1, 1), {status}, "Completed", {division}, "EU") + COUNTIFS({Shipment date}, <=DATE(2023, 1, 31), {Shipment date}, >=DATE(2023, 1, 1), {status}, "Completed", {division}, "EU", {Production Date}, <>"")
seems to be doing what i wanted. So basically it's gathering all projects with Production date in place and then adding projects without Production date based on the same conditions but by Shipment date
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!