Average if a separate column has data

[JOHANNA]

I have a spreadsheet with our company's divisions in one column (B) and number of days to fill a position in a separate column (O). I need a formula that will allow me to average column O if column B has "Sales" in the cell.

I have tried to create a VLOOKUP formula, but I get 0 for the result when there is data in column O.

Any help is greatly appreciated!!

 

[T. Valko]

I have a spreadsheet with our company's divisions in one column (B) and number of days to fill a position in a separate column (O). I need a formula that will allow me to average column O if column B has "Sales" in the cell.

I have tried to create a VLOOKUP formula, but I get 0 for the result when there is data in column O.

Any help is greatly appreciated!!

Try one of these...

This array formula** will work in any version of Excel...

=AVERAGE(IF(B2:B100="Sales",O2:O100))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

This normally entered formula will work in Excel 2007 and later...

=AVERAGEIF(B2:B100,"Sales",O2:O100)

Adjust the ranges to suit.

 

[Aladin Akyurek]

I have a spreadsheet with our company's divisions in one column (B) and number of days to fill a position in a separate column (O). I need a formula that will allow me to average column O if column B has "Sales" in the cell.

I have tried to create a VLOOKUP formula, but I get 0 for the result when there is data in column O.

Any help is greatly appreciated!!

Some options...

=SUMIF(B:B,"Sales",O:O)/MAX(1,COUNTIF(B:B,"Sales"))

On Excel 2007 or later:

=IFERROR(AVERAGEIF(B:B,"Sales",O:O),0)

 

[JOHANNA]

I entered the formula like this
{=AVERAGE(IF(Detail!B:B="Sales",Detail!O:O,0))}

and received a respose of 3. There are three cells and they average 202.

 

[T. Valko]

I entered the formula like this
{=AVERAGE(IF(Detail!B:B="Sales",Detail!O:O,0))}

and received a respose of 3. There are three cells and they average 202.

If you're using Excel 2003 or earlier then you can't use entire columns as range references in array formulas. Use smaller specific ranges.

Also, don't include the 0. That will cause incorrect results.

Array entered**:

=AVERAGE(IF(Detail!B2:B100="Sales",Detail!O2:O100))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[T. Valko]

Hmmm...

When I quoted that last reply it said you got a #NUM! error.

 

[JOHANNA]

Aladdin,
That worked wonderfully!! Thank you very much you saved me hours of work!

 

[Aladin Akyurek]

Aladdin,
That worked wonderfully!! Thank you very much you saved me hours of work!

Glad to hear that. Thanks for providing feedback.

 

[JOHANNA]

I did have a #NUM! error, but it was my fault, I didn't add the Detail! page to the formula for the O column. When I entered the page name it gave 3 instead of the #NUM1. I thought I could edit the reply before anyone had a chance to respond.

Thank you very much for your prompt responses. You're wonderful!

 

[T. Valko]

I did have a #NUM! error, but it was my fault, I didn't add the Detail! page to the formula for the O column. When I entered the page name it gave 3 instead of the #NUM1. I thought I could edit the reply before anyone had a chance to respond.

Thank you very much for your prompt responses. You're wonderful!

Ok, thanks for the feedback!