SOLVED

Excel Countifs return true if OR. (?)

%3CLINGO-SUB%20id%3D%22lingo-sub-2428513%22%20slang%3D%22en-US%22%3EExcel%20Countifs%20return%20true%20if%20OR.%20(%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2428513%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20sure%20Countifs%20counted%20with%20AND%2C%20when%20all%20logical%20test%20was%20true.%20But%20This%20one%20list%20if%20one%20of%20the%20conditions%20is%20met.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCountifs(B4%3AB9%3BB2%3BC4%3AC9%3BE4%3AE9)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22excel%20countifs.PNG%22%20style%3D%22width%3A%20718px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F287233iCF3CED9F5419FA0F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22excel%20countifs.PNG%22%20alt%3D%22excel%20countifs.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20excplanation%20to%20that%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%20is%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%3C%2FP%3E%3CP%3E-%20Geir%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2428513%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2428587%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Countifs%20return%20true%20if%20OR.%20(%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2428587%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECan%20you%20explain%20in%20detail%20what%20you%20want%20to%20accomplish%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2428628%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Countifs%20return%20true%20if%20OR.%20(%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2428628%22%20slang%3D%22en-US%22%3EHi%20hans%2C%20I%20want%20to%20count%20how%20many%20times%20Italia%20is%20in%20column%20B%2C%20and%20the%20ressult%20is%20a%20draw.%20Column%20C%20%3D%20Column%20E.%20I%20th%20Excampe%20the%20line%20with%20Italia%201%20Wales%201.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2428725%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Countifs%20return%20true%20if%20OR.%20(%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2428725%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20you%20have%20found%2C%20COUNTIFS%20is%20not%20the%20correct%20function%20for%20this.%20COUNTIFS(C4%3AC9%2CE4%3AE9)%20compares%20every%20cell%20in%20C4%3AC9%20with%20every%20cell%20in%20E4%3AE9%20instead%20of%20C4%20with%20E4%2C%20C5%20with%20E5%20etc.%3C%2FP%3E%0A%3CP%3EYour%20SUMPRODUCT%20formula%20is%20the%20one%20to%20use.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Regular Contributor

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)

 

excel countifs.PNG

 

Any excplanation to that?

 

Example is attached.

 

Best Regards

- Geir

 

7 Replies

@Geir Hogstad 

Can you explain in detail what you want to accomplish?

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.
best response confirmed by Geir Hogstad (Regular Contributor)
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.

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

@Geir Hogstad

For example:

 

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

@Geir Hogstad 

My understanding what is behind

image.png

 

Thank you for your explanation Sergei.