Countifs- if cell blank then count by other cell

Piotr Majkowski
Piotr Majkowski ✭✭✭
edited 04/26/23 in Formulas and Functions

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

  • Piotr Majkowski
    Piotr Majkowski ✭✭✭
    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

  • Paul Newcome
    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.

  • 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
    Piotr Majkowski ✭✭✭
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!