Sum/average numbers in column A dependant on value in column B

[Guest]

I would like to sum/average numbers in column A, only if for that row, the
value in column B is a certain value.
e.g. Average column A if value in column B is 1. Then again, average of
column A if value in column B is 2 etc.
A B
4 1
5 2
8 1
7 2
Thanks

 

[Peo Sjoblom]

=AVERAGE(IF(B1:B4=1,A1:A4))

entered with ctrl + shift & enter


--

Regards,

Peo Sjoblom


http://nwexcelsolutions.com

 

[Zack Barresse]

Hi Sue,

If you want to Sum, check out SUMIF. If you want to Average, you'll either
need a SumProduct or an array formula...

=Average(If(B1:B4=1,A1:A4))

Confirm this array formula with Ctrl + Shift + Enter.

HTH

 

[Bob Phillips]

=AVERAGE(IF($B$1:$B$10=1,$A$1:$A$10))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)