average and if

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

 

[JE McGimpsey]

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

 

[Bernard Liengme]

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

 

[Bernard Liengme]

REMEMBER I hate Spell Check!

 

[Don Guillett]

French word?

 

[Guest]

Thanks - that gives me what I need!

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

 

[Guest]

Your first formula also only works in XL2007 (since it uses an entire column).