COUNTIFS with various values?

Michael Ott

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

Atus Bartal

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

Michael Ott

Thanks Atus, that's exactly what I needed. 

 

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

 

Best regards

M

Nuribe

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?

 

J. Craig Williams

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