sumproduct question please
 

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??
--
Thanks as always

Lise

sumproduct question please
 

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

--
Jacob


"Lise" wrote:

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??
--
Thanks as always

Lise

sumproduct question please
 

"Lise" wrote:
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 -----

"Lise" wrote in message
...
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??
--
Thanks as always

Lise

sumproduct question please
 

=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)


--
Jacob


"Lise" wrote:

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??
--
Thanks as always

Lise

sumproduct question please
 

Thankyou both, fabulous
--


Lise


"Joe User" wrote:

"Lise" wrote:
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 -----

"Lise" wrote in message
...
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??
--
Thanks as always

Lise

.