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
.
|