Need help with COUNTIF

Options

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")))

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Scott Orsey ✭✭✭✭✭
    Options

    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

    If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!

  • tgattsh
    tgattsh ✭✭✭✭
    Options

    @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...

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!