AVERAGEIF using multiple string compares

[Thunderclap911]

Hello All!
I have an AVERAGEIF formula that has multiple wildcard string compare conditions in the same column. It doesn't appear to be searching properly and I believe the problem is, it's only looking at one condition. Here is that I have below:

=AVERAGEIF(MBR!A:A,{"*Civil*","*Shim*","*Power*","*GO&G*"},MBR!H:H)

Any advice would be greatly appreciated please!

[63falcondude]

Try this:

=AVERAGE(IF(ISNUMBER(SEARCH({"Civil","Shim","Power","GO&G"},MBR!A$2:A$1000)),MBR!H$2:H$1000)) Ctrl Shift Enter

Adjust the ranges to suit your need. Just don't use whole column references.

[Thunderclap911]

Hrrmm... I doesn't appear to be grabbing all of the fields. I think I failed to explain properly, the string criteria need to be "OR" instead of "AND". If any of those strings show in column MBR!A$2:A$1000 then they need to be included.

[63falcondude]

Yes, that is what the formula from post #2 should be doing.

If you attach a small representative sample of your data along with the desired result (which you can enter manually), I'll have a look and see what is going on.

Also, make sure that you are entering the formula using Ctrl+Shift+Enter and not just Enter like you do with normal formulas.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

[Thunderclap911]

Ok, that's fantastic!! Thank you again for all your help!!! The formula in question in on the "EIC" tabl and it is the E9 cell.

[63falcondude]

Looks like there are blank cells in column H. Should those be ignored or treated as zero?

If blanks should be ignored, simply add this part (in red):

=AVERAGE(IF(ISNUMBER(SEARCH({"Civil","Shim","Power","GO&G"},MBR!A$2:A$1000))*(MBR!H$2:H$1000<>""),MBR!H$2:H$1000)) Ctrl Shift Enter

I get 94.85

[Thunderclap911]

This works great, thank you so much!! One last question for you please. If I would like to add another criteria to only pull for a particular month list on another tab, would I just add it like this to the formula?

=AVERAGE(IF(ISNUMBER(SEARCH({"Civil","Shim","Power","GO&G"},MBR!A$2:A$1000))*(MBR!H$2:H$1000<>"")*(MBR!B:B,"*"&'stoplight chart_CS '!L1&"*"),MBR!H$2:H$1000))

[Thunderclap911]

ok... well I tried that and it did not work The intent of this added constraint is to make it so that all the user has to do is change the month on the first tab, and the rest of the monthly formulas automatically change. Your help would be greatly appreciated!

[63falcondude]

No, you can't use wildcards like that in an array formula. Also, B:B should be B$2:B$1000.

Simply correcting the syntax would look like this:

=AVERAGE(IF(ISNUMBER(SEARCH({"Civil","Shim","Power","GO&G"},MBR!A$2:A$1000))*(MBR!H$2:H$1000<>"")*(ISNUMBER(SEARCH('stoplight chart_CS '!L1,MBR!B$2:B$1000))),MBR!H$2:H$1000)) Ctrl Shift Enter

If that doesn't do what you are expecting, we will need to see a small sample of the data and expected results.

[63falcondude]

Looking at the "sample" sheet from post #5, column B of the MBR sheet is the same as L1 of the stoplight_chart_CS sheet.

Why do you want to use a wildcard for that?

You should be able to simply use this:

=AVERAGE(IF(ISNUMBER(SEARCH({"Civil","Shim","Power","GO&G"},MBR!A$2:A$1000))*(MBR!H$2:H$1000<>"")*('stoplight chart_CS '!L1=MBR!B$2:B$1000),MBR!H$2:H$1000)) Ctrl Shift Enter

[63falcondude]

Better yet, this can be done without an array formula.

Since average is sum/count, you can use this:

=SUM(SUMIFS(MBR!H:H,MBR!A:A,{"*Civil*","*Shim*","*Power*","*GO&G*"},MBR!H:H,"<>",MBR!B:B,'stoplight chart_CS '!L1))/SUM(COUNTIFS(MBR!A:A,{"*Civil*","*Shim*","*Power*","*GO&G*"},MBR!H:H,"<>",MBR!B:B,'stoplight chart_CS '!L1))

[Thunderclap911]

I guess I was reaching waaaaaay back to my old coding days when I used a lot of wildcards searches in queries. You solution is clearly much more elegant that my hack job haha. Sincerely, thank you so much for your help!! I've got a lot to learn :p

[63falcondude]

Happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.