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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sue - 29 Mar 2006 18:25 GMT

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

Reply to this Message

Peo Sjoblom - 29 Mar 2006 18:35 GMT

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

entered with ctrl + shift & enter

Signature

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

>I would like to sum/average numbers in column A, only if for that row, the
> value in column B is a certain value.
[quoted text clipped - 6 lines]
> 7 2
> Thanks

Reply to this Message

Zack Barresse - 29 Mar 2006 18:36 GMT

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

Signature

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

>I would like to sum/average numbers in column A, only if for that row, the
> value in column B is a certain value.
[quoted text clipped - 6 lines]
> 7 2
> Thanks

Reply to this Message

Bob Phillips - 29 Mar 2006 18:39 GMT

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

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> I would like to sum/average numbers in column A, only if for that row, the
> value in column B is a certain value.
[quoted text clipped - 6 lines]
> 7 2
> Thanks

Reply to this Message