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

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

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

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

entered with ctrl + shift & enter


--

Regards,

Peo Sjoblom


http://nwexcelsolutions.com



"Sue" wrote in message
...
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

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

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

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Sue" wrote in message
...
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

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

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

"Sue" wrote in message
...
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