Average data in column IF specific text in another

[iherndon]

Having some trouble working this out, I keep getting #VALUE!.

I have two columns, Column A that indicates Staffed or Unstaffed, and Column B which has data I want to average. I'm attempting to enter a formula into a cell elsewhere that will basically only calculate the Column B data if Column A contains the text 'Staffed'.

Thanks for any assistance!

 

[T. Valko]

Something else just occurred to me; if I want to base the average off of fields with "staffed" and another keyword, how would the second criteria be formatted into the formula? Also, is it case sensitive?

Thanks!

Is the other keyword in the same column as "staffed" or is it in another column?

If it's in the same column then what you want is an average if column A = "staffed" OR "other_keyword". If the other keyword is in a different column then you want an average if column A = "staffed" AND other column = "other_keyword".

Case is not a factor (unless you want it to be).

 

[iherndon]

Is the other keyword in the same column as "staffed" or is it in another column?

If it's in the same column then what you want is an average if column A = "staffed" OR "other_keyword". If the other keyword is in a different column then you want an average if column A = "staffed" AND other column = "other_keyword".

Case is not a factor (unless you want it to be).

Yes, they would be two totally separate columns. Thank you again for your helpful reply.

 

[iherndon]

Right now I'm working on the exact formatting to use for it, but believe it to not be correct so far.

 

[Aladin Akyurek]

Right now I'm working on the exact formatting to use for it, but believe it to not be correct so far.

Care to re-cap the ranges and the criteria these ranges must meet?

 

[iherndon]

Actually I think I JUST got it.

=AVERAGEIFS(P9:P268, B9:B268, "Smith, John", J9:J268, "Staffed")

I left out the s in averageifs. P is the column with Response Time data I was looking to average based on specific rows in column B being a specific name while J also contain Staffed.

 

[T. Valko]

Actually I think I JUST got it.

=AVERAGEIFS(P9:P268, B9:B268, "Smith, John", J9:J268, "Staffed")

I left out the s in averageifs. P is the column with Response Time data I was looking to average based on specific rows in column B being a specific name while J also contain Staffed.

OK, the formula is syntactically correct.

Good deal. Thanks for the feedback!

 

[Aladin Akyurek]

Actually I think I JUST got it.

=AVERAGEIFS(P9:P268, B9:B268, "Smith, John", J9:J268, "Staffed")

I left out the s in averageifs. P is the column with Response Time data I was looking to average based on specific rows in column B being a specific name while J also contain Staffed.

Right... Thanks for providing feedback.

 

[iherndon]

Really opens up a lot of options with the types of reporting I'm looking to do!

Right now I have two criteria that would be AND. After getting the AVERAGEIFS function to work, I'm seeing a need to have a third criteria that excludes any that contain a specific word.

Using Excel 2007's Function Arguments window, I've selected the fields for Criteria_range3 and am now looking to ender the Criteria3 to exclude cells with specific text. looks like it only gives the option for =?

 

[T. Valko]

Really opens up a lot of options with the types of reporting I'm looking to do!

Right now I have two criteria that would be AND. After getting the AVERAGEIFS function to work, I'm seeing a need to have a third criteria that excludes any that contain a specific word.

Using Excel 2007's Function Arguments window, I've selected the fields for Criteria_range3 and am now looking to ender the Criteria3 to exclude cells with specific text. looks like it only gives the option for =?

<> means not equal

To exclude some item you would define it as "<>whatever".

For example, to exclude cells in column X that contain "yes":

=AVERAGEIFS(P9:P268, B9:B268, "Smith, John", J9:J268, "Staffed", X9:X268,"<>Yes")

 

[iherndon]

Aah that worked, thanks! Is there anything special to do in this example?

The keyword I want to use is for any cell that contains the letters 'delay'. <>delay obviously covers that, but I seem to be missing some that perhaps had additional letters such as 'delayed' being present in the field's text. Does it have to be exact; or can I <> the text 'delay' and 'delayed' with just 1 criteria?