average and if

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a column of dollar amounts and a column of categories. I want to
average all cells in the dollar amount column that equal a certain text. For
instance, I want the average of a range of cells in Col A if Col B = HOTEL.
This seems like it should be simple, but it is eluding me.
 
One way:

Array-entered (CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF(B1:B100="HOTEL",A1:A100))

An alternative:

=SUMIF(B:B,"HOTEL",A:A)/COUNTIF(B:B,"HOTEL")
 
This array function works =AVERAGE(IF(B:B="hotel",A:A,""))
Redeemer to commit with CTRL+SHIFT+ENTER

This non-array (commit with ENTER) also works
=SUMIF(B:B,"hotel",A:A)/COUNTIF(B:B,"hotel")

And this non-array works only in XL2007
=AVERAGEIF(B1:B7,"hotel",A1:A7)

best wishes
 
Thanks - that gives me what I need!

JE McGimpsey said:
One way:

Array-entered (CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF(B1:B100="HOTEL",A1:A100))

An alternative:

=SUMIF(B:B,"HOTEL",A:A)/COUNTIF(B:B,"HOTEL")
Click to expand...
 
Back
Top