COUNTIFS COUNTM to count the number of instances in multi-select columns, based on several criteria?
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
-
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
-
Thank You Kelly!
I doubt I'd figure out that syntax on my own. It works perfectly! :)
Answers
-
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.
-
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
-
Thank You Kelly!
I doubt I'd figure out that syntax on my own. It works perfectly! :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!