I'm trying to put together a formula that will count the number of times the
data in column a matches c.
I need a total column in this case(row 6) should say 2 because text in
columns a & c only match 3 times

Say i have:
a b c d e
1 c1 c1
2 c2 c1
3 f3 f3
4 p2 f3
5 t4 t4
6

I don't understand the logic of displaying 2 when there are 3 direct
matches (c1/c1, f3/f3 and t4/t4).

Please explain.

Pete

On Jan 24, 12:20*am, Araseli
wrote:
I'm trying to put together a formula that will count the number of times the
data in column a matches c.
I need a total column in this case(row 6) should say 2 because text in
columns a & c only match 3 times

Say i have:
* *a * b * c * d * e
1 c1 * * *c1
2 c2 * * *c1 *
3 f3 * * * f3
4 p2 * * *f3
5 t4 * * *t4
6

Each row has to match to be counted, right?

=SUMPRODUCT(--(A1:A5=B1:B5))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Araseli wrote:

I'm trying to put together a formula that will count the number of times the
data in column a matches c.
I need a total column in this case(row 6) should say 2 because text in
columns a & c only match 3 times

Say i have:
a b c d e
1 c1 c1
2 c2 c1
3 f3 f3
4 p2 f3
5 t4 t4
6

--

Dave Peterson

Hi,

If they are only counted as matches if they are on the same row then

=SUM(N(A1:A5=B1:B5))

Entered as an array (press Shift+Ctrl+Enter to enter the formula)

If the matches are case sensitive (A2 is not equal to a2) then

=SUMPRODUCT(1*EXACT(A1:A5,B1:B5))
or
=SUMPRODUCT(--EXACT(A1:A5,B1:B5))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Araseli" wrote:

I'm trying to put together a formula that will count the number of times the
data in column a matches c.
I need a total column in this case(row 6) should say 2 because text in
columns a & c only match 3 times

Say i have:
a b c d e
1 c1 c1
2 c2 c1
3 f3 f3
4 p2 f3
5 t4 t4
6

I am so sorry it should have said 3 instead of 2.. but someone else already
gave me an answer.
Thanks you for time!

"Pete_UK" wrote:

I don't understand the logic of displaying 2 when there are 3 direct
matches (c1/c1, f3/f3 and t4/t4).

Please explain.

Pete

On Jan 24, 12:20 am, Araseli
wrote:
I'm trying to put together a formula that will count the number of times the
data in column a matches c.
I need a total column in this case(row 6) should say 2 because text in
columns a & c only match 3 times

Say i have:
a b c d e
1 c1 c1
2 c2 c1
3 f3 f3
4 p2 f3
5 t4 t4
6

Thank you so much!
it worked!

"Dave Peterson" wrote:

Each row has to match to be counted, right?

=SUMPRODUCT(--(A1:A5=B1:B5))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Araseli wrote:

I'm trying to put together a formula that will count the number of times the
data in column a matches c.
I need a total column in this case(row 6) should say 2 because text in
columns a & c only match 3 times

Say i have:
a b c d e
1 c1 c1
2 c2 c1
3 f3 f3
4 p2 f3
5 t4 t4
6

--

Dave Peterson

Thank you so much for your time!

"Shane Devenshire" wrote:

Hi,

If they are only counted as matches if they are on the same row then

=SUM(N(A1:A5=B1:B5))

Entered as an array (press Shift+Ctrl+Enter to enter the formula)

If the matches are case sensitive (A2 is not equal to a2) then

=SUMPRODUCT(1*EXACT(A1:A5,B1:B5))
or
=SUMPRODUCT(--EXACT(A1:A5,B1:B5))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Araseli" wrote:

I'm trying to put together a formula that will count the number of times the
data in column a matches c.
I need a total column in this case(row 6) should say 2 because text in
columns a & c only match 3 times

Say i have:
a b c d e
1 c1 c1
2 c2 c1
3 f3 f3
4 p2 f3
5 t4 t4
6