Countifs formula help

Hi There,

I've been looking all afternoon to see how I can complete my countifs formula and I think I'm close.


I need to count the number of rows between a date range based on if another column states a certain value. So in the case below, if the date is between X and Y in the 'date of incident' column and if the department involved is selected as fabrication.


So far i have

=COUNTIFS([Date of Incident]:[Date of Incident], AND((@cell >= DATE(2023, 8, 1), @cell <= DATE(2023, 8, 31))), AND([department

its the last part i cant get working, where it will count the rows if the department involved is filtered to fabrication.


Any help would be greatly appreciated.



Best Answer

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Answer ✓

    Hi @James Brett

    Yes, you can add ranges and criterion. For instance, regarding your first example, the formula might look something like:

    =COUNTIFS([Date of Incident]:[Date of Incident], AND(@cell >= DATE(2023, 8, 1), @cell <= DATE(2024, 8, 1)), [Department Involved]:[Department Involved], "Fabrication", [Department Involved]:[Department Involved], AND("Fabrication", "Design"))

    Note that I've not checked this formula. I've simply expanded the syntax provided in the COUNTIFS Function

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

Answers

  • Hopefully the above makes sense

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

    Hi @James Brett

    Would the following work for you?

    =COUNTIFS([Date of Incident]:[Date of Incident], AND(@cell >= DATE(2023, 8, 1), @cell <= DATE(2024, 8, 1)), [Department Involved]:[Department Involved], "Fabrication")

    This is based on the syntax of the COUNTIFS Function

    COUNTIFS( range1 criterion1 [ range2​criterion2​... ])

    You'll note the reduction in brackets around the AND function, and there's no need to start the second (and subsequent) range/criterion with an AND.

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Hi Jason,

    This is great thanks, seems to have worked. Just out of curiosity, is it possible to put another set of criteria in the equation, so for example, if the department involved had fabrication and design in the cell, as well just fabrication, or we also wanted to filter a project name in the 'project name and customer name' column?


    kr

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Answer ✓

    Hi @James Brett

    Yes, you can add ranges and criterion. For instance, regarding your first example, the formula might look something like:

    =COUNTIFS([Date of Incident]:[Date of Incident], AND(@cell >= DATE(2023, 8, 1), @cell <= DATE(2024, 8, 1)), [Department Involved]:[Department Involved], "Fabrication", [Department Involved]:[Department Involved], AND("Fabrication", "Design"))

    Note that I've not checked this formula. I've simply expanded the syntax provided in the COUNTIFS Function

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Ideal thanks Jason much appreciated,

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!