Excel – Formula to calculate between two columns

Ask a question


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.

Excel - Autofilter stopped filtering!
Excel - How to merge several rows into a single one