Excel – Formula to calculate between two columns

December 2016



Issue


I need to calculate between two columns. Such as:

If there is written (A2:A100) "aminul", then count how many times was written (B2:B100) "ok".

If there is written (A2:A100) "aminul", then count how many times was written (B2:B100) "Not ok".

If there is written (A2:A100) "ratna", then count how many times was written (B2:B100) "ok".

If there is written (A2:A100) "ratna", then count how many times was written (B2:B100) "not ok".

Solution


Try with this formula :

Case 1 :
= SUMPRODUCT((A1:A14="aminul")*(B1:B14="ok"))

Case 2 :
= SUMPRODUCT((A1:A14="aminul")*(B1:B14="Not ok")) 

Case 3 :
= SUMPRODUCT((A1:A14="ratna")*(B1:B14="ok")) 

Case 4 :
= SUMPRODUCT((A1:A14="ratna")*(B1:B14="Not ok")) 

Best regards

Note


Thanks to aquarelle for this tip on the forum.

Related :

This document entitled « Excel – Formula to calculate between two columns » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.