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!

 

[njimack]

Confirm with ctrl+shift+enter:
=AVERAGE(IF(B2:B100="Staffed",A2:A100))

 

[VoG]

Try

=SUMIF(A1:A10,"Staffed",B1:B10)/COUNTIF(A1:A10,"Staffed")

 

[iherndon]

Confirm with ctrl+shift+enter:
=AVERAGE(IF(B2:B100="Staffed",A2:A100))

When doing the work manually, I get an avg of 10min35sec (00:10:35). I started with that to check if I am doing the formula correctly. Using this formula I got a result that was somewhere around 15min. It's definitely not including Unstaffed bc that avg is more around 1hr, but not sure where the difference of 10 and 15 is coming from.

Try

=SUMIF(A1:A10,"Staffed",B1:B10)/COUNTIF(A1:A10,"Staffed")

Using this I get an average that's double the time if I didn't filter to just staffed to start. I wonder if this sumif/countif formula is moreso giving a % of total that are staffed?

Basically the goal is to calculate from one week to the next what staffed response time would be for a given range. The range can vary from one week to the next, but with the right formula I was just going to manually modify the #range since I think including blank cells in an average would skew it. Right now rows 7-169 are the rows that have data for Staffing period in column A, and Response Time in Column B.

 

[VoG]

Works here and ignores blank cells

Excel Workbook
	A	B	C	D
1	Staffed	00:10		00:20
2	Unstaffed	00:20
3	Staffed	00:30
Sheet1

 

[T. Valko]

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!

If you're using Excel 2007 or later...

=AVERAGEIF(A2:A10,"staffed",B2:B10)

 

[iherndon]

Ah I figured out what I was doing wrong! Both of those worked thanks! I'm using 2007 so that second shorter formula worked too.

 

[T. Valko]

Ah I figured out what I was doing wrong! Both of those worked thanks! I'm using 2007 so that second shorter formula worked too.

Good deal. Thanks for the feedback!

 

[iherndon]

If you're using Excel 2007 or later...

=AVERAGEIF(A2:A10,"staffed",B2:B10)

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!

 

[Aladin Akyurek]

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!

Have a look at the AVEREGEIFS function.