CODEWITHSUNDEEP
excel
Vinod K
Vinod K

Reputation: 2143

Comparing 2 columns in excel using CountIF

Having difficulty comparing the 2 columns to get the result

list 1     List 2

AT.aaa      Yes
SM.aaa      No
CM.aaaa     Yes
DV.aaaa     Sorry
SI.aaaa     NA
AT.bbb      yes
AT.cccc     NA
SM.cccc     Sorry
CM.bbb      No

My Motive is to fill this table

      Yes   No   Sorry    NA
AT     2     0     0      1
SM     0     1     1      0
DV
CM
SI

I am able to find the count of Yes, No. and sorry individually using Countif but am no able to find the value of those with the other parameter. Can you guys help me out.

Upvotes: 0

Views: 1002

Answers (1)

Scott Craner
Scott Craner

Reputation: 152650

I would use the COUNTIFS() formula:

=COUNTIFS($A:$A,"*" & $D2 & "*",$B:$B,E$1)

enter image description here

Upvotes: 4

Related Questions