Count cells that match?

[meredithsegal]

I have data in C2:C14 and D2:D14. I want Excel to count the number of instances in which the value in column C matches the value in that row in column D. What's the easiest way?

Thanks so much--you guys are always awesome!

 

[Jonmo1]

Try

=SUMPRODUCT(--(C2:C14=D2:D14))

 

[meredithsegal]

Awesome, why does it work tho?

 

[Jonmo1]

Awesome, why does it work tho?

Because I told it to.


It creates an array of True or False results based on the expression C2:C14=D2:D14.
Evaluating each row 1 at a time, does C2 = D2 ? Does C3 = D3?
With a True or False result for each row

The -- converts those True/False results into 1/0 results (True = 1, False = 0)
Then it sums the results.

 

[meredithsegal]

(and how can I amend it only to consider cells with a given value in column A -- how to add (countif(A2:A14,"apple"))

 

[Jonmo1]

(and how can I amend it only to consider cells with a given value in column A -- how to add (countif(A2:A14,"apple"))

Try
=SUMPRODUCT(--(A2:A14="apple"),--(C2:C14=D2:D14))

 

[meredithsegal]

You're awesome--would you like a job?

 

[Jonmo1]

Glad to help, thanks for the feedback.