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

MKa

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

Kelly Moore

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

Thank You Kelly!

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

MKa

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

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

Thank You Kelly!

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