COUNTIFS condition

[spamitovic]

Hi all,

I'm starting to work on Excel, and I need assistance in this function:

=COUNTIFS(C7:C23;$AH$4;C7:C23;$AH$5)

Using just 1 condition it works, but adding more conditions I got 0 value in my result cel.

AH4 and AH5 contains just a code like A or M.

Any suggestion?

Thank you in advance.

[#2 · Accepted solution]

@spamitovic The criteria in a COUNTIFS statement counts cells that meet all criteria. In your case, you are trying to count cells in C7:C23 that are both A and M. Hence, zero. The following formula will probably give you the result you are after.

=COUNTIF(C7:C23;$AH$4)+COUNTIF(C7:C23;$AH$5)

[#3]

@spamitovic 

When both the criteria ranges are the same, only one condition can be evaluated as True and another condition would be evaluated as False.

Think it like this.

When Countifs formula starts counting, it takes the first cell C7 which is the first cell of both the criteria ranges and then it first checks if C7 is equal to H4 and suppose C7 is equal to H4, it returns True for the first condition and then it checks to see whether the same cell C7 is equal to H5 and which cannot be True because C7 is already equal to H4 and cannot be equal to the second criteria H5 at the same time and therefore the Countifs formula returns False.

Remember that multiple criteria work like AND condition so your Countifs formula can be translated as...

Count the occurrences where C7:C23 = H4 AND C7:C23 = H5 and in this case only one condition would be evaluated as True unless both the criteria are same.

So you should try the formula like below...

=COUNTIFS(C7:C23,$AH$4)+COUNTIFS(C7:C23,$AH$5)

[#4]

@spamitovic 

As variant

=SUM(COUNTIFS(C7:C23;$AH$4:$AH$5))

[#5]

@Subodh_Tiwari_sktneer 

Best explanation. Thank you so muche it solve my problem.

[#6]

@spamitovic 

You're welcome! Glad you found it helpful.