Countif depending on 1)string value 2)date range

Joe Suh

Fault type Date raised

Microbial 01/1/2020

Microbial 12/1/2020

Chemical 14/1/2020

Chemical 01/2/2020

Physical 01/3/2020

I have been successful in getting the count for the [fault type] through (e.g.'microbial')

=COUNTIFS({Supplier Performance Range 1}, FIND("Microbial", @cell) > 0)

But I would like to also do this by month, as in real life there will be more than just one incident.

any ideas?

Mike Wilday

So, for instance, you want to see all of the incidents for January 2020?

Try this variation: =COUNTIFS({Supplier Performance Range 1}, FIND("Microbial", @cell) > 0, [Date Raised]:[Date Raised], Month(@cell) = 1,[Date Raised]:[Date Raised], Year(@cell) = 2020)

The only thing you should note is that if your Date Raised formula is a cross sheet reference, you'll have to create those ranges as Cross sheet references instead of just listing the range.

Mike Wilday

So, for instance, you want to see all of the incidents for January 2020?

Try this variation: =COUNTIFS({Supplier Performance Range 1}, FIND("Microbial", @cell) > 0, [Date Raised]:[Date Raised], Month(@cell) = 1,[Date Raised]:[Date Raised], Year(@cell) = 2020)

The only thing you should note is that if your Date Raised formula is a cross sheet reference, you'll have to create those ranges as Cross sheet references instead of just listing the range.

Joe Suh

wow I was expecting a few days for the reply.. I'm currently exploring Smartsheet as an alternative from MS Sharepoint/Power automate... the questions I asked months ago, still no reply... that's a tick! I will give this a try :) thanks so much Mike - I will reply again once i get this working.

Mike Wilday

Sounds good! :) let us know if you get stuck. There are several of us on here in the community that are fairly quick to reply!

If you get stuck, providing screenshots (hiding sensitive data) also can help. And if at any point you need to share the sheet that is also a possibility. :) Happy Smartsheeting!

Andrée Starå

Hi Joe,

@Joe Suh

Welcome to the Community and the wonderful world of Smartsheet!

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD