CountIf

Mindy Schneider

Hello super smart Smartsheet friends. Here is what I'm needing...

I want a TOTAL COUNT of "task" for Past 7 Days. I thought I would put it as a summary field but then a Metric on my dashboard. Task is multi-select so I will be doing a summary field for each task option.

Any thoughts?

Kelly Moore

That's a good question. Multi-select dropdowns require us to use either HAS or CONTAINS when looking at the cell contents. HAS is an exact-match function. CONTAINS is not. So, for example, if you had responses Voucher (singlular) and Vouchers (plural), CONTAINS would pick up both if you searched for Voucher (singular).

The Collect function is where you can continue to filter your data. It has the syntax of (range you want collected, range1, criteria1, range2, criteria2, etc). You can continue to add criteria to the Collect function always adding a range-criteria pair.

If you don't have too many choices in your dropdown, you'll be able to build a formula for each choice. If you have numerous responses, you could build a metric sheet where each of your dropdown choices had their own row. If you have to go this route and need help, shout out and the community can help you with the cross functional formulas.

But here's the formula you asked for your summary sheet where you would build a formula for each choice.

=COUNTM(COLLECT(Task:Task, Created:Created, @cell >= TODAY(-7), Created:Created, @cell <= TODAY(), Task:Task, CONTAINS("Meal Voucher", @cell)))

does this work for you?

cheers

Kelly

Kelly Moore

Hi @mmschneider113031

Try this approach using the COUNTM function and the collect function. COUNTM was specifically added to count items in multi-select dropdowns.

=COUNTM(COLLECT(Task:Task, Created:Created, @cell >= TODAY(-7), Created:Created, @cell <= TODAY()))

cheers

Kelly

Mindy Schneider

This worked great but super dumb question - how do I count specific items? Like Count of "meal vouchers"?

Thanks for all your help!

Kelly Moore

That's a good question. Multi-select dropdowns require us to use either HAS or CONTAINS when looking at the cell contents. HAS is an exact-match function. CONTAINS is not. So, for example, if you had responses Voucher (singlular) and Vouchers (plural), CONTAINS would pick up both if you searched for Voucher (singular).

The Collect function is where you can continue to filter your data. It has the syntax of (range you want collected, range1, criteria1, range2, criteria2, etc). You can continue to add criteria to the Collect function always adding a range-criteria pair.

If you don't have too many choices in your dropdown, you'll be able to build a formula for each choice. If you have numerous responses, you could build a metric sheet where each of your dropdown choices had their own row. If you have to go this route and need help, shout out and the community can help you with the cross functional formulas.

But here's the formula you asked for your summary sheet where you would build a formula for each choice.

=COUNTM(COLLECT(Task:Task, Created:Created, @cell >= TODAY(-7), Created:Created, @cell <= TODAY(), Task:Task, CONTAINS("Meal Voucher", @cell)))

does this work for you?

cheers

Kelly

Mindy Schneider

YOU ROCK! Thank you, Thank you! That is exactly what I needed! ❤ I love smartsheet but those long formulas look like a different language to me!

Kelly Moore

🙂 You're very welcome. Like any different language, the more you use it, the more familiar it becomes. You also have a whole community of 'translators' here to help on your journey.

cheers,

Kelly