COUNTIFS COUNTM to count the number of instances in multi-select columns, based on several criteria?

Options

Hello,

I have been trying to create a formula that would count the total number of instances in multi-select columns, based on criteria in cross-referenced sheets, but sadly with no success. To illustrate what I'm trying to do please see the example below:


From my reference data in Sheet1 - I would like to receive a sum of errors in Findings 1 and Findings 2 columns for the distinct types of protocols - if the date falls within the last 7 days (for the referenced sheet this would be date greater than 25-Oct-2022).

Sheet 1 (the source data sheet):


The referenced sheet contains only 3 types of protocols, but the actual list is much longer so I would prefer to refer to their name by referencing the Protocols row below (by using [Protocols]@row rather than protocol name in brackets like "1221"):

Sheet 2 (the result sheet):


The desired output for the Total findings # would be:

1221: 4 (3 x Findings 1 and 1 x Findings 2 - from dates 28 and 29 Oct)

1222: 4 (1 x Findings 1and 3 x Findings 2 - from dates 25 and 26 Oct)

1223: 0 (the count doesn't fall within the desired date)

Up to date I was scouring the forums and tried several combinations (COUNTM(COLLECT)), COUNTIFS - but none of them succeeded in calculating what I want, based on all of my criteria.

The below formula does the job fine for calculating based on protocol, but I cannot insert the date criteria to it:

=COUNTM(COLLECT({Findings 1}, {Protocol}, [Protocols]@row)) + COUNTM(COLLECT({Findings 2}, {Protocol}, [Protocols]@row))

Any help would be greatly appreciated.

Thanks!

Marta

Best Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @MKa

    Great job on finding a workaround. A working formula is a good formula in my book. In the future, in case you don't want to add the helper column to the source sheet, the syntax for filtering by the last 7 days of data is

    {your date column on source sheet}, AND(@cell >= TODAY(-7), @cell <= TODAY())

    because dates can sometimes be finnicky in formulas, I usually add an ISDATE() to weed out any non-date responses that might be in the column.

    {your date column on source sheet}, AND(ISDATE(@cell), @cell >= TODAY(-7), @cell <= TODAY())

    Excellent job on answering your own question. We look forward to hearing more questions from you- it helps us all learn something.

    Kelly

  • MKa
    MKa
    Answer ✓
    Options

    Thank You Kelly!

    I doubt I'd figure out that syntax on my own. It works perfectly! :)

Answers

  • MKa
    Options

    If anyone is curious:

    I ended up creating a helper checkbox column in Sheet 1 to mark the dates that fall within the last 7 days:

    =IF(AND([Date]@row >= TODAY(-7), [Date]@row <= TODAY()), 1)

    and then included that in my existing formula

    =COUNTM(COLLECT({Findings 1}, {Helper checkbox column}, =1, {Protocol}, [Protocols]@row)) + COUNTM(COLLECT({Findings 2}, {Helper checkbox column}, =1, {Protocol}, [Protocols]@row))


    Curiously it had to be inserted before the '{Protocol}, [Protocols]@row' columns or it failed (calculated the correct value +1... I dont know why).

    Still would prefer a solution without helper columns, as I truly hate them ;)

    But I'm glad it works.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @MKa

    Great job on finding a workaround. A working formula is a good formula in my book. In the future, in case you don't want to add the helper column to the source sheet, the syntax for filtering by the last 7 days of data is

    {your date column on source sheet}, AND(@cell >= TODAY(-7), @cell <= TODAY())

    because dates can sometimes be finnicky in formulas, I usually add an ISDATE() to weed out any non-date responses that might be in the column.

    {your date column on source sheet}, AND(ISDATE(@cell), @cell >= TODAY(-7), @cell <= TODAY())

    Excellent job on answering your own question. We look forward to hearing more questions from you- it helps us all learn something.

    Kelly

  • MKa
    MKa
    Answer ✓
    Options

    Thank You Kelly!

    I doubt I'd figure out that syntax on my own. It works perfectly! :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!