Countifs- if cell blank then count by other cell

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

Find more posts tagged with

Accepted answers

Piotr Majkowski

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

All comments

Paul Newcome

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.

Piotr Majkowski

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

Piotr Majkowski

FIY for anyone struggling with similar problem in the future

like always sleep helped and i came up with solution that works:

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.