Excel 2007 : AVERAGEIFS including several conditions

**milliemoo** · 11-16-2011, 06:00 AM

Hello, Me again!

I am using the formula below:

=AVERAGEIFS(AB:AB,X:X,"Kenyan Shift",Y:Y,">0",AA:AA,">0",D:D,"Yes")

to calculate average response times for investigations. It is working fine but I need to add another condition and the formula I have tried isn't working. In addition to the formula above, I need to add to it so that it only includes cells if the value in column W = EITHER "HS1, HS2, or HS3. I know how to write the formula to include just one additional condition but I don't know how to make it look for either of these three terms.

Is anyone able to assist, please?

**Miraun** · 11-16-2011, 07:11 AM

I worked on it for a few minutes, then found this reply from NBVC to a similar thread a while ago:

http://www.excelforum.com/excel-work...functions.html

Apparently if you want to integrate in the OR function, you're looking at stepping away from the Averageifs and looking towards the fun fun Sumproduct function.

Unfortunately, I'm still pretty terrible with sumproduct, but I know the others on the forums are good with it, and I'm sure one of them will be by shortly!

-waits-

**milliemoo** · 11-16-2011, 08:27 AM

Thank you for your efforts! I'll see what I can do with the SUMPRODUCT function. Failing that, I'll do a table somewhere else to calculate each one individually and then another formula based on that!

**zbor** · 11-16-2011, 08:36 AM

Or you can upload example workbook...

**daddylonglegs** · 11-16-2011, 10:06 AM

you can use a SUMIFS/COUNTIFS type setup like this

=SUM(SUMIFS(AB:AB,X:X,"Kenyan Shift",Y:Y,">0",AA:AA,">0",D:D,"Yes",W:W,{"HS1","HS2","HS3"}))/SUM(COUNTIFS(X:X,"Kenyan Shift",Y:Y,">0",AA:AA,">0",D:D,"Yes",W:W,{"HS1","HS2","HS3"}))

......or if HS1, HS2, and HS3 are the only possible 3 character entries beginning with HS (no HS7 or HSx possible in column W for instance) then you could go back to AVERAGEIFS - with a wildcard, i.e.

=AVERAGEIFS(AB:AB,X:X,"Kenyan Shift",Y:Y,">0",AA:AA,">0",D:D,"Yes",W:W,"HS?")

**milliemoo** · 11-16-2011, 11:06 AM

daddylonglegs.....I knew you'd save the day! Thank you.....I'll try it now.

**milliemoo** · 11-16-2011, 11:39 AM

It works! I have used the first formula as there are other HS entires that can appear. I need to repeat this formula but for other groups of HS numbers. Thank you so much for your assistance. I hope you guys either get paid a lot of money to do this or absolutely love doing it!