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
Check out the Formula Handbook template!