Apply COUNTIF Function in Multiple Ranges for Same Criteria

Question

Apply COUNTIF Function in Multiple Ranges for Same Criteria

Anonymous

Hi

Please guide how to combine the following double Countif formula into single Countif function by using AND function.

=COUNTIF($C2:$H2,C2)+COUNTIF($L2:$Q2,C2)=2

Regards

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

0 additional answers

Answer 1

=COUNTIF($C2:$H2,C2)+COUNTIF($L2:$Q2,C2)=2

That doesn't make really a sense, since the criteria in C2 is ALWAYS found in $C2:$H2

If you want to check that you have only one occurrence in C2:H2 and one in L2:Q2

R2: =AND(COUNTIF($C2:$H2,C2)=1,COUNTIF($L2:$Q2,C2)=1)

.

If you want to check if the positions match use

R2: =COUNTIFS(C2:H2,C2,L2:Q2,C2)=1

.

Combine them if you want to check both
R2: =AND(COUNTIF($C2:$H2,C2)=1,COUNTIF($L2:$Q2,C2)=1,COUNTIFS(C2:H2,C2,L2:Q2,C2)=1)

.

Andreas.

Answer 2

Hi FazliWahid,

Thank you for reaching out. My name is Leo, a user just like you. I'd be happy to help you with your concern.

I have modified your formula, kindly try it and see if it works.

=AND(COUNTIF($C2:$H2,C2)>0, COUNTIF($L2:$Q2,C2)>0)

If both conditions are true, the AND function returns TRUE. Otherwise, it returns FALSE.

Your understanding and patience will be highly appreciated. I hope I provided a helpful information regarding your concern! Let me know if you have any further questions. Be safe always!

Best Regards,

Leo

Share via

Apply COUNTIF Function in Multiple Ranges for Same Criteria

0 additional answers