Comparing 2 columns in excel using CountIF

Question

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.

Scott Craner · Accepted Answer · 2016-05-18 18:53:20Z

4

I would use the COUNTIFS() formula:

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

answered May 18, 2016 at 18:53

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Vinod K Over a year ago

Thanks Scott, Can you help me understand the formula ?

Scott Craner Over a year ago

COUNTIFS() allows multiple ranges and criteria, it is AND based. The first is using wildcards "*" both before and after the criteria to find the strings that contain the criteria. This will find anything that has the value in column D in the values of Column A. Then the second is just a straight forward find.

Scott Craner Over a year ago

@VinodK you would put it in E2 and drag across and down.

Vinod K Over a year ago

Thanks a lot for your help , Scott!

Collectives™ on Stack Overflow

Comparing 2 columns in excel using CountIF

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related