MEDIAN with multiple criteria and named range

[rogeraz]

I need help with a MEDIAN formula. I have one tab (ResolutionTimes!) with a list of tickets (for a helpdesk environment), with several columns:

B2:B10000 has the time it took for the helpdesk to close the ticket.
C2:C10000 has the “Priority” of the ticket, which can have a value of 1, 2, 3, or 4.
D2:D10000 has the “Group” that the ticket was closed by, which can have many possible values.

I have several Named Ranges in the workbook that correspond to a set of “Groups”.

Named Range Examples:

NamedRange1:
Purple
Green
Blue
Yellow
Red

NamedRange2:
Orange
Violet
Tan
Brown

(Those are just examples, the real named ranges have many more Groups under them than this.)

What I want to do is get a Median of the time it took to close the ticket with the following conditions:
- Priority of 3
- Closed by any of the groups in NamedRange1

Here’s what I have, but it’s giving a #N/A error, I’ve tried many different variations:

{=MEDIAN(IF((MATCH(NamedRange1,ResolutionTimes!$D$2:$D$10000,0))*(ResolutionTimes!$C$2:$C$10000=3),ResolutionTimes!$B$2:$B$10000))}

The point of the named ranges is trying to avoid having to list out each Group separately that I want to match on (I'll be doing many of these formulas for the different ranges.)

Thanks in advance for any help.

Windows 7, Excel 2007

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=MEDIAN(
    IF(ISNUMBER(MATCH(ResolutionTimes!$D$2:$D$10000,NamedRange1,0)),
    IF(ResolutionTimes!$C$2:$C$10000=3),
      ResolutionTimes!$B$2: $B$10000)))

 

[rogeraz]

That's a beautiful thing, works perfectly. Thanks Aladin.

Control+shift+enter, not just enter:

=MEDIAN(
   IF(ISNUMBER(MATCH(ResolutionTimes!$D$2:$D$10000,NamedRange1,0)),
   IF(ResolutionTimes!$C$2:$C$10000=3),
     ResolutionTimes!$B$2: $B$10000)))

 

[Aladin Akyurek]

That's a beautiful thing, works perfectly. Thanks Aladin.

You are welcome. Thanks for providing feedback.