Max, Min, & Std Dev with Multiple Criteria Including a Wild Card

**rmmohan** · 06-14-2013, 11:22 AM

I've searched for the solution to this problem and have been unable to find it. So here goes my first post!

As the title states I'm trying to determine the max/min/std dev of a set of data based on multiple criteria.
The criteria however can sometimes not be selected at all.

I was able to solve for the average using the following formula:
=AVERAGEIFS(Data!D2:D10,Data!A2:A10,Results!B2,Data!B2:B10,Results!B3,Data!C2:C10,Results!B4)
This is because the function averageifs accepts the symbol * as a wild card.

I've seen suggestions to use max(if(
but the if function does not accept wildcards to the best of my knowledge.

I would really appreciate any help or suggestions.
I have attached a file with a simplifed version of my problem.

Thank you in advance!!

**shg** · 06-14-2013, 12:03 PM

=MAX(IF((Data!A2:A10=Results!B2)*(Data!B2:B10=Results!B3)*(Data!C2:C10=B4), Data!D2:D10))

... confirmed with Ctrl+Shift+Enter

**Fotis1991** · 06-14-2013, 12:11 PM

OR this regular formula.

=SUMPRODUCT(MAX((Data!A2:A10=Results!B2)*(Data!B2:B10=Results!B3)*(Data!C2:C10=B4)*Data!D2:D10))

**shg** · 06-14-2013, 12:20 PM

@Fotis, that works as long as the max value is >=0

**Fotis1991** · 06-14-2013, 12:24 PM

Correct(as always) teacher!

Thank you.

I just tried that in this worksheet and as gives the same result i did this suggestion.

**rmmohan** · 06-14-2013, 12:45 PM

Thank you so much for the help!
I tried using Fotis' formula but I'm just having one issue.
If in my example the chosen criteria is, Criteria 1: A, Criteria 2: CC, and Criteria 3: *
The max calculated is zero.
It should be 6.
Any suggestions?