Countifs without including duplicates


Im trying to get information to show on a dashboard that breaks down events by type and date range. I was able to get the following formula to work on a roll up sheet:

=COUNTIFS({[sheet] Range 2}, <=DATE(2021, 1, 31), {[sheet] Range 2}, >=DATE(2021, 1, 1), {[sheet] Range 1}, "task type")

the problem is that multiple rows in my sheet are tied to the same work order number so I end up with duplicates, which inflates the numbers for the month.

Im trying to figure out how to add the "work order number" column to this formula so that it only counts each work order once if it falls within the month and meets the "task type" criteria.

Best Answer


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try something like this...

    =COUNT(DISTINCT(COLLECT({Work Order # Column}, {Date Column}, IFERROR(YEAR(@cell), 0) = 2021, {Type Column}, @cell = "task type")))

  • Paul, Thank you! I replaced the IFERROR section of the formula and replaced it with the date range from my sample formula so I pulled just the orders from the month.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    I used the IFERROR/YEAR because I misread your original dates and thought you were pulling for all of 2021. My apologies.

  • no worries, the IFERROR/YEAR is still helpful to me because im unfamiliar with how to use that formula so now I'll be able to use it going forward.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You can also combine that with IFERROR/MONTH to specify a year and month instead of having to manually enter the first and last date.

    In the below formula you will see an example of that as well as an example of how to combine criteria for the same range without having to repeat the range multiple times. It comes in handy if you have a lot of different ranges with multiple criteria each or if you have quite a few different criteria sets for the same range.

    =COUNT(DISTINCT(COLLECT({Work Order # Column}, {Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021), {Type Column}, @cell = "task type")))

    We put all of our {Date Column} criteria together without having to repeat the range, and we specified a month and year instead of a start and end date.

  • wow, super helpful. thanks again!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!