Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

COUNTIFS with various values?

Michael Ott
edited 12/09/19 in Archived 2016 Posts

Hi all,


I'm trying to count the number of times my brands are used in each state, but the column I'm trying to query has three different values. The data looks like this


State            Brand

State 1          Brand A

State 1          Brand A

State1           Brand B

State 2         Both

State 2         Brand A


Since "Both" includes two brands, I will need to count it twice. I was hoping to get the countif examine the same range twice, but getting zeroes. 


My countif formula looks like this:

=COUNTIFS(State6:State28, "State 1", Brand6:Brand28, "Brand A", Brand6:Brand28, "Both")


How can I modify it so it includes both "Brand A" and "Both" in the tally?





  • Atus Bartal
    Atus Bartal ✭✭✭✭✭✭


    This is because in =COUNTIFS formulas the given conditions are in AND logical relationship, if you know what I mean. You should put them in with OR: 

    =COUNTIFS(State6:State28, "State 1", Brand6:Brand28, OR(@cell = "Brand A", @cell = "Both"))



  • Michael Ott

    Thanks Atus, that's exactly what I needed. 


    I wasn't sure if I could use Boolean with countifs. 


    Best regards


  • Nuribe


    Im also trying to use 3 values for a countif formula:


    =COUNT([% Work Complete]4:[% Work Complete]245 < 1, IF(AND(Department4:Department245, "Process Engineering", Health4:Health245, "On Time")))

    This formula keeps on returning a 2, any ideas?


  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭


    Yes. you are returning 2 because you are counting two things.

    [% Work Complete]4:[% Work Complete]245 < 1


    IF(AND(Department4:Department245, "Process Engineering", Health4:Health245, "On Time")

    return value of 1 and together they count to 2.

    The formula you are looking for is COUNTIFS not COUNT

    Are you trying to count the number of rows with a [% Work Complete] < 0.5 and Heath = "On Time" and Department = "Process Engineering" ?

    Then this should work:

    =COUNTIFS([% Work Complete]4:[% Work Complete]245, <1, Department4:Department245, "Process Engineering", Health4:Health245, "On Time")

    This is 3 ranges each with a criteria:

    =COUNTIFS ( range1, criteria1, range2, criteria2, range3, criteria3)

    Note that the size of the ranges need to be the same.

    I hope this helps.

    Also, this is a very old thread. Not many people monitor older threads and you might not get an answer to your question. If you post a new one, you are more likely to get a response next time.






This discussion has been closed.