Excel Countifs return true if OR. (?)

[Geir Hogstad]

Hi,

I was sure Countifs counted with AND, when all logical test was true. But This one list if one of the conditions is met.

=Countifs(B4:B9;B2;C4:C9;E4:E9)

Any excplanation to that?

Example is attached.

Best Regards

- Geir

[#2]

@Geir Hogstad 

Can you explain in detail what you want to accomplish?

[#3]

Hi hans, I want to count how many times Italia is in column B, and the ressult is a draw. Column C = Column E. I th Excampe the line with Italia 1 Wales 1.

[#4 · Accepted solution]

@Geir Hogstad 

As you have found, COUNTIFS is not the correct function for this. COUNTIFS(C4:C9,E4:E9) compares every cell in C4:C9 with every cell in E4:E9 instead of C4 with E4, C5 with E5 etc.

Your SUMPRODUCT formula is the one to use.

[#5]

Thank you. The problem with sumproduct her, is taht it also count
Italia Sveits
So I guess I have to add one more condition.
/Geir

[#6]

@Geir Hogstad

For example:

=SUMPRODUCT((B4:B9=B2)*(C4:C9=E4:E9)*(C4:C9<>""))

[#7]

@Geir Hogstad 

My understanding what is behind

[#8]

Thank you for your explanation Sergei.