average and if
 

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.

average and if
 

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



In article ,
ccparch57 wrote:

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.

average and if
 

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 V Liengme
www.stfx.ca/people/bliengme
remove caps from email

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

average and if
 

REMEMBER I hate Spell Check!
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
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 V Liengme
www.stfx.ca/people/bliengme
remove caps from email

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

average and if
 

French word?

--
Don Guillett
SalesAid Software

"Bernard Liengme" wrote in message
...
REMEMBER I hate Spell Check!
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
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 V Liengme
www.stfx.ca/people/bliengme
remove caps from email

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

average and if
 

Thanks - that gives me what I need!

"JE McGimpsey" wrote:

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



In article ,
ccparch57 wrote:

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.

average and if
 

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

"Bernard Liengme" wrote:

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 V Liengme
www.stfx.ca/people/bliengme
remove caps from email

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