CountIF text string criteria by day, week, month

Sarbuland Bangash

Hi Everyone,

Wanted to provide a bit of a background before posing the question, I had set up an intake form which collects information of events. To analyze the raw data, I set up another worksheet to where I could keep all the formulas. In the second worksheet, I am trying to count the number of times a specific event is occurring by day/week/month and the second criteria placed would be the name of the event for which I already have a column set up being referenced. I have provided the formula below that I am using, what am I doing wrong, as I would really like to learn and enhance my knowledge for formulas within smartsheets.

=IF({Incident Log with Form Range 2} = TODAY(), COUNTIF({Incident Log with Form Range 1}, [Incident Type]@row), " ")

PS. this formula was only for calculating today, but ideally I would like one for Day, Week & Month. I have also attached shots of both worksheets, any assistance would be much appreciated.

Metric calculation sheet.PNG

Information Intake Sheet.PNG

Thanks in advance!

Genevieve P.

Hi @Sarbuland Bangash

You've almost got it! The IF Function can't look at an entire column and see if the cell = Today, though. Instead, you'll want to add this criteria actually within your COUNTIF function, making it COUNTFS (plural).

Try this, for Today's formula:

=COUNTIFS({Incident Log with Form Range 1}, [Incident Type]@row, {Incident Log with Form Range 2}, TODAY())

You can then use this same structure but look for a specific date with the DATE function:

=COUNTIFS({Incident Log with Form Range 1}, [Incident Type]@row, {Incident Log with Form Range 2}, DATE(2020, 10, 31))

Note: you can also reference a date within your helper sheet, instead of using DATE(), like this:

{Incident Log with Form Range 2}, [Date Column]@row

Week:

Then you can look within 7 days of today:

=COUNTIFS({Incident Log with Form Range 1}, [Incident Type]@row, {Incident Log with Form Range 2}, <= TODAY(), {Incident Log with Form Range 2}, >= TODAY(-7))

Or within the same week as Today's week, using WEEKNUMBER:

=COUNTIFS({Incident Log with Form Range 1}, [Incident Type]@row, {Incident Log with Form Range 2}, WEEKNUMBER(TODAY()))

Note: you can also reference a date within your helper sheet, instead of using TODAY(), like this:

{Incident Log with Form Range 2}, WEEKNUMBER([Date Column]@row)

Month:

Then for looking through a MONTH, you can use the MONTH function. Either put TODAY() as the "date" within the function if you're looking for today's month, or you can use MONTH(@cell) = 1 to find a specific month. This one is looking for January (1), but you can change the number depending on what you're looking for.

Note that with the MONTH function, you may want to wrap an IFERROR around it just in case it errors when looking at blank cells.

Try this, for January:

=COUNTIFS({Incident Log with Form Range 1}, [Incident Type]@row, {Incident Log with Form Range 2}, IFERROR(MONTH(@cell), 0) = 1)

Let me know if this structure makes sense to you and you're able to get your formulas working!

Cheers,

Genevieve

Genevieve P.

Hi @Sarbuland Bangash

You've almost got it! The IF Function can't look at an entire column and see if the cell = Today, though. Instead, you'll want to add this criteria actually within your COUNTIF function, making it COUNTFS (plural).

Try this, for Today's formula:

=COUNTIFS({Incident Log with Form Range 1}, [Incident Type]@row, {Incident Log with Form Range 2}, TODAY())

You can then use this same structure but look for a specific date with the DATE function:

=COUNTIFS({Incident Log with Form Range 1}, [Incident Type]@row, {Incident Log with Form Range 2}, DATE(2020, 10, 31))

Note: you can also reference a date within your helper sheet, instead of using DATE(), like this:

{Incident Log with Form Range 2}, [Date Column]@row

Week:

Then you can look within 7 days of today:

=COUNTIFS({Incident Log with Form Range 1}, [Incident Type]@row, {Incident Log with Form Range 2}, <= TODAY(), {Incident Log with Form Range 2}, >= TODAY(-7))

Or within the same week as Today's week, using WEEKNUMBER:

=COUNTIFS({Incident Log with Form Range 1}, [Incident Type]@row, {Incident Log with Form Range 2}, WEEKNUMBER(TODAY()))

Note: you can also reference a date within your helper sheet, instead of using TODAY(), like this:

{Incident Log with Form Range 2}, WEEKNUMBER([Date Column]@row)

Month:

Then for looking through a MONTH, you can use the MONTH function. Either put TODAY() as the "date" within the function if you're looking for today's month, or you can use MONTH(@cell) = 1 to find a specific month. This one is looking for January (1), but you can change the number depending on what you're looking for.

Note that with the MONTH function, you may want to wrap an IFERROR around it just in case it errors when looking at blank cells.

Try this, for January:

=COUNTIFS({Incident Log with Form Range 1}, [Incident Type]@row, {Incident Log with Form Range 2}, IFERROR(MONTH(@cell), 0) = 1)

Let me know if this structure makes sense to you and you're able to get your formulas working!

Cheers,

Genevieve

Sarbuland Bangash

Hi Genevieve,

Problem solved! thanks for getting back to me.

Genevieve P.

Hi @Sarbuland Bangash

No problem at all! I'm glad I could help.