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]

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?

We can use a wildcard like this...

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

 

[iherndon]

That did it! I added just one character and it fixed it totally haha. Thx!

 

[T. Valko]

That did it! I added just one character and it fixed it totally haha. Thx!

You're welcome. Thanks for the feedback!

 

[mrmo]

I also have a similar dilemma. I want to do the same thing as the original poster, but I want to match specific text in TWO different columns. I tested out the "=AVERAGE(IF(B2:B100="Staffed",A2:A100))" CSE formula and it works great for matching one column, but I need to match data from column B AND from column C, then average the numbers in another column. I tried to nest the AND function and I couldn't get it to work. Can anyone please help me with this? Thanks!

 

[Aladin Akyurek]

I also have a similar dilemma. I want to do the same thing as the original poster, but I want to match specific text in TWO different columns. I tested out the "=AVERAGE(IF(B2:B100="Staffed",A2:A100))" CSE formula and it works great for matching one column, but I need to match data from column B AND from column C, then average the numbers in another column. I tried to nest the AND function and I couldn't get it to work. Can anyone please help me with this? Thanks!

Control+shift+enter, not just enter:

=AVERAGE(IF(Range1=Cond1,IF(Range2=Cond2,AverageRange)))

Substitute the relevant ranges and cells for the placeholders this formula uses.

 

[T. Valko]

I also have a similar dilemma. I want to do the same thing as the original poster, but I want to match specific text in TWO different columns. I tested out the "=AVERAGE(IF(B2:B100="Staffed",A2:A100))" CSE formula and it works great for matching one column, but I need to match data from column B AND from column C, then average the numbers in another column. I tried to nest the AND function and I couldn't get it to work. Can anyone please help me with this? Thanks!

Try one of these...

Book1
	B	C	D
2	West	Yes	27
3	North	No	38
4	North	No	73
5	South	Yes	83
6	North	Yes	61
7	West	No	18
8	South	No	14
9	West	No	96
10	South	Yes	63
11	North	Yes	86
12	West	No	30
13	West	Yes	5
14	South	No	38
15	North	Yes	45
Sheet1

We want to average column D where column B = North and column C = Yes.

This array formula** will work in ANY version of Excel:

=AVERAGE(IF(B2:B15="North",IF(C2:C15="Yes",D2:D15)))

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

The normally entered formula will work in Excel 2007 and later:

=AVERAGEIFS(D2:D15,B2:B15,"North",C2:C15,"Yes")

 

[mrmo]

Thank YOU!!!

This formula worked perfectly. You both are the greatest!

 

[T. Valko]

Thank YOU!!!

This formula worked perfectly. You both are the greatest!

You're welcome. Thanks for the feedback!

 

[Aladin Akyurek]

Thank YOU!!!

This formula worked perfectly. You both are the greatest!

You are welcome. I appreciate the feedback.