sumproduct?

[Guest]

Can anyone provide a way to perform the following function. I've been told
there is a way to do with "sumproduct" which I use.

Column A and B each have values in 100+ rows. Each cell in the column has
one of several different #'s in them.

If there is a "1" in the cell of a given row in in column A and a "2" in the
cell of the same row in column B, what is the average of values cells of the
same row in column C. i.e. i.e. give the average of values in column C
restricting to rows where there is a "1" in column A and a "2" in column B.

Can this function be done in Excel? Or am i stuck with SPSS or some such.

anand

 

[Guest]

This is not a SUMPRODUCT solution, but it should work:
=AVERAGE(IF(A1:A200=1,IF(B1:B200=2,C1:C200)))
This is an array formula. You need to press Ctl+Shift+Enter to make it work
(not jsut Enter).
If you entered it correctly, you will see
{=AVERAGE(IF(A1:A200=1,IF(B1:B200=2,C1:C200)))} in the formula bar (you don't
type the {} yourself).
Adjust the range size to your needs.

 

[Roger Govier]

Hi

You have an array solution from tj which gives you your required result.
You specifically asked about doing it with Sumproduct, (which is not an
array solution), so for completeness here is a Sumproduct answer

=SUMPRODUCT(--(A1:A20=1),--(B1:B20=2),C1:C20)/SUMPRODUCT(--(A1:A20=1),--(B1:B20=2))
Change ranges to suit

Regards

Roger Govier