Need help with COUNTIF

tgattsh

When I use this formula, it works fine,

=COUNTIFS([Date Request Submitted -System]:[Date Request Submitted -System], <=TODAY(), [Date Request Submitted -System]:[Date Request Submitted -System], >TODAY(-7)) + COUNTIFS(Status:Status, OR(@cell = "Submitted", @cell = "Acknowledged", @cell = "Revisit Underway"))

As soon as I add a 3rd range,criteria it states #NESTED CRITERIA.

=COUNTIFS([Date Request Submitted -System]:[Date Request Submitted -System], <=TODAY(), [Date Request Submitted -System]:[Date Request Submitted -System], >TODAY(-7)) + COUNTIFS(Status:Status, OR(@cell = "Submitted", @cell = "Acknowledged", @cell = "Revisit Underway") + COUNTIFS([Assigned To]:[Assigned To], OR(@cell = "Glenn Frazer", @cell = "Alan Jochim", @cell = "Anthony Double")))

Paul Newcome

You have a misplaced closing parenthesis. Take one away from the very end and use it to close out the second COUNTIFS before adding the third COUNTIFS.

Although... Exactly what are you wanting to count with this? Your current formula will count up all rows based on each COUNTIFS individually regardless of the data in the others. Basically, your first COUNTIFS is going to count all rows that are greater than TODAY(-7) and disregard the Status and Assigned to. Then your second is going to count all rows that have one of those statuses regardless of the date and Assigned to. Then your third COUTNIFS will count all rows that have one of those people assigned regardless of date and status. Then you are going to add all of those counts together.

If you are looking to count only rows that meet all of those range/criteria sets, you would need to put all range/criteria sets into a single COUNTIFS.

Paul Newcome

You have a misplaced closing parenthesis. Take one away from the very end and use it to close out the second COUNTIFS before adding the third COUNTIFS.

Although... Exactly what are you wanting to count with this? Your current formula will count up all rows based on each COUNTIFS individually regardless of the data in the others. Basically, your first COUNTIFS is going to count all rows that are greater than TODAY(-7) and disregard the Status and Assigned to. Then your second is going to count all rows that have one of those statuses regardless of the date and Assigned to. Then your third COUTNIFS will count all rows that have one of those people assigned regardless of date and status. Then you are going to add all of those counts together.

If you are looking to count only rows that meet all of those range/criteria sets, you would need to put all range/criteria sets into a single COUNTIFS.

Scott Orsey

Hi @tgattsh , You have a parentheses out of place. Try:

=COUNTIFS([Date Request Submitted -System]:[Date Request Submitted -System], <=TODAY(), [Date Request Submitted -System]:[Date Request Submitted -System], >TODAY(-7)) + COUNTIFS(Status:Status, OR(@cell = "Submitted", @cell = "Acknowledged", @cell = "Revisit Underway")) + COUNTIFS([Assigned To]:[Assigned To], OR(@cell = "Glenn Frazer", @cell = "Alan Jochim", @cell = "Anthony Double"))

Be well

tgattsh

@Scott Orsey @Paul Newcome Thanks for your help.

@Paul Newcome Somehow you knew exactly what I was trying to get to. Thanks for the added context...

Paul Newcome

Happy to help. 👍️