sumproduct question please

L

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??
 
J

Joe User

Lise said:
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 -----
 
J

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

Lise

Thankyou both, fabulous
--


Lise


Joe User said:
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 -----



.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top