#### 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?

Options
edited 12/09/19

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?

Thanks!

Michael

• ✭✭✭✭✭✭
Options

Michael,

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

Atus

• Options

Thanks Atus, that's exactly what I needed.

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

Best regards

M

• Options

Hi,

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?

• ✭✭✭✭✭✭
Options

Nuribe,

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

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

and

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.

Craig

This discussion has been closed.