How to use a nested if with count

ardhoogland

We would like to have all the criteria for a created date by week. the number of new helpdesk tickets created by each end user for a given week.

Example: Week of 7/7/25 Mickey submitted 5 tickets, Donald Duck submitted 10 tickets, part of Donalds 10 tickets only 5 were reported in week 7/7/25.

Report

Week of 7/7:

Name: Count:

Mickey 5

Donald. 5

Week of 6/30:

Name: Count:

Donald 5

This is our attempt:

=COUNTIFS([Date reported]15:[Date reported]34, WEEKNUMBER(34), [Reported by]15:[Reported by]34, "persons name")

Advise how we can write this, please.

Ard

jmyzk_cloudsmart_jp

Hi @ardhoogland

You are close.

=COUNTIFS([Reported By]15:[Reported By]34, Name@row , [Date Reported]15:[Date Reported]34, WEEKNUMBER(@cell ) = WEEKNUMBER(DATE(2025, 7, 7)))

https://app.smartsheet.com/b/publish?EQBCT=33a6c5c809dd4faeab9d5e3f7a2fc386

As I tested, the COUNTIF formula in Date condition and the Reported by condition did not work.

=COUNTIFS([Date Reported]15:[Date Reported]34, WEEKNUMBER(@cell ) = WEEKNUMBER(DATE(2025, 7, 7), [Reported By]15:[Reported By]34, Name@row ))

=COUNTIFS([Date Reported]15:[Date Reported]34, WEEKNUMBER(@cell ) = WEEKNUMBER(DATE(2025, 7, 7), [Reported By]15:[Reported By]34, Name21)
=COUNTIFS([Date Reported]15:[Date Reported]34, WEEKNUMBER(@cell ) = WEEKNUMBER(DATE(2025, 7, 7), [Reported By]15:[Reported By]34, "Mickey"))

They all ignored the Reported by condition to return 13.

I also added a formula that does not use row numbers, such as [Date Reported]15, in the Sheet Summary fields.

'=COUNTIFS([Reported By]:[Reported By], "Mickey", [Date Reported]:[Date Reported], WEEKNUMBER(@cell ) = WEEKNUMBER(DATE(2025, 7, 7)))