CODEWITHSUNDEEP
excelexcel-formula
joehua
joehua

Reputation: 735

Excel: countifs on the same range doesn't work?

I have a list of members with their birthday. I'd like to count the number of members whose birth month falls in July or August. I tried the following COUNTIFS formula but it did not work

=COUNTIFS(AH3:AH135,"=7",AH3:AH135,"=8")------ formula(1) gave zero

while individual COUNTIFS worked fine

=COUNTIFS(AH3:AH135,"=7") gave 13 and 
=COUNTIFS(AH3:AH135,"=8") gave 6

I then defined the range AH3:AH135 as month and tried the following formula. It still did not work.

=COUNTIF(month,"=7",month,"=8") gave zero

I even tried the following formula and it gave 4, not the correct answer

=COUNTIFS(AH3:AH135,OR("=7","=8"))------- formula(2)

I had to resort to the following formula to get the correct answer

=SUM(COUNTIFS(AH3:AH135,{"=7","=8"}) gave 19

My questions are:

  1. Why didn't formula(1) work? The only reason I can think of is that ranges must be unique. Can't have the same range more than once in the formula.

  2. Why did formula(2) give 4? I have no clue what it was doing.

Upvotes: 1

Views: 272

Answers (1)

Gangula
Gangula

Reputation: 7332

  1. The reason the formula 1 didn't work is because the formula is looking for values "7" AND "8" (which is 0). That how COUNTIFS formula works. It looks for values that match all the criteria, not either of the criteria.
  2. And the reason formula 2 gave you for is probably because there are few errors in the range "AH3:AH135" because the formula OR("=7","=8") results to an error. and if its counting the number of errors in your range.

Solution:

The formula that you could use in your case is =COUNTIF(AH3:AH135,"=7") + COUNTIF(AH3:AH135,"=8")

Upvotes: 1

Related Questions