0
Thanks

A few words of thanks would be greatly appreciated.

Excel – Formula to calculate between two columns

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.

0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel – Formula to calculate between two columns », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).