sumproduct question please

[Lise]

Hi everyone

I have a list of names in column a - I then have data in various other
columns which is copied from other worksheets (Columns B, F and J are names)
I want to sum how many time a name in column a is listed in the others so
have entered as:

=SUMPRODUCT(($B$2:$B$500=A2)*($F$2:$F$500=A2)*($J$2:$J$500=A2))

But this is not calculating correctly - can someone pls advise what step I'm
missing??

 

[Jacob Skaria]

Try
=COUNTIF(A:A,A1)+COUNTIF(F:F,A1)+COUNTIF(J:J,A1)

 

[Joe User]

I want to sum how many time a name in column a is listed in the others so
have entered as:
=SUMPRODUCT(($B$2:$B$500=A2)*($F$2:$F$500=A2)*($J$2:$J$500=A2))

When you use "*" in that context, it is behaving like AND. In other words,
it is counting how many times A2 appears in __all_three__ columns in the
same row.

I think you want:

=SUMPRODUCT(($B$2:$B$500=A2)+($F$2:$F$500=A2)+($J$2:$J$500=A2))

That will count the number of instances of A2 in any of the three columns.

In other words, if A2 appears in two columns in a particular row, it will
add 2 to the count.

If, instead, your intent is to count how many rows that A2 appears
__at_least_once__ in any of the threes columns, you might want:

=SUMPRODUCT(--(($B$2:$B$500=A2)+($F$2:$F$500=A2)+($J$2:$J$500=A2)>0))

That will add 1 to the count if A2 appears in one, two or all three columns
in the same row.


----- original message -----

 

[Jacob Skaria]

=SUMPRODUCT(($B$2:$B$500=A2)+($F$2:$F$500=A2)+($J$2:$J$500=A2))

OR

=COUNTIF(B:B,A1)+COUNTIF(F:F,A1)+COUNTIF(J:J,A1)

 

[Lise]

Thankyou both, fabulous
--


Lise

When you use "*" in that context, it is behaving like AND. In other words,
it is counting how many times A2 appears in __all_three__ columns in the
same row.

I think you want:

=SUMPRODUCT(($B$2:$B$500=A2)+($F$2:$F$500=A2)+($J$2:$J$500=A2))

That will count the number of instances of A2 in any of the three columns.

In other words, if A2 appears in two columns in a particular row, it will
add 2 to the count.

If, instead, your intent is to count how many rows that A2 appears
__at_least_once__ in any of the threes columns, you might want:

=SUMPRODUCT(--(($B$2:$B$500=A2)+($F$2:$F$500=A2)+($J$2:$J$500=A2)>0))

That will add 1 to the count if A2 appears in one, two or all three columns
in the same row.


----- original message -----



.