data searching and counting

**teladria** · 10-19-2009, 07:48 PM

I'm trying to program a function that will search a column for a specific data set and then compare it to data from another column. Then I want it to count the number of times that the data from one column is the same as the other. I tried:

=sum((countif (A2:a45, "Yes"), countif(b2:b45, "Yes")) I realized that it would just add all of the yeses. I do have the latest version of Excel and it seems to have more functions. Is there a comparison function?

English translation - Compare a2 to b2. If they are both yes, count 1. Then do this for each cells in column a and column b.

**mewingkitty** · 10-19-2009, 08:42 PM

You're looking for a way to count the number of repetitions of a given cell in a column in another column...?

I think?

mew!

Attached example of wild guess? :P

**JBeaucaire** · 10-19-2009, 09:17 PM

If A2:A45 = Yes/No answers
If B2:B45 = Yes/No answers
If C1 = Yes
If C2 = No

Put this formula D1 and copy down:
=SUMPRODUCT(--($A$2:$A$45 & $B$2:$B$45 = C1 & C1))

**teladria** · 10-26-2009, 03:46 PM

None of those formulae work. I do want a comparison but I want a comparison cell to cell. If the comparison is the same text I want it to count 1.

**JBeaucaire** · 10-26-2009, 04:56 PM

That's what mine does.

=SUMPRODUCT(--($A$2:$A$45 & $B$2:$B$45 = C1 & C1))
...or another way to look at it:
=SUMPRODUCT(--($A$2:$A$45 & $B$2:$B$45 = "YesYes"))