"MEDIANIF" Array combining two ranges

[Ronny Hamida]

I know that there have been many posts regarding using MEDIAN Arrays (in
place for the missing "MEDIANIF" function in Excel.)

I was wondering if it can be taken a step further!

Currently, I am using a median array that only performs the MEDIAN function
if the four left characters match what I specify, however can I specify two
sets of data instead of one? Here's what I have now:

{=MEDIAN(IF((LEFT('Sheet1'!$J$2:$J$5000,4)="1234"),'Sheet1'!$A$2:$A$5000,""))}

As you can see, it is scanning the first four characters of column "J" and,
if it matches "1234" it will use the value in column "A" for the MEDIAN
function.

The problem is that I want to take the median of values that start with
"1234" and "5678" at the same time. Is this possible? I think I had already
tried a nested IF statement, but I'm thinking I did it incorrectly because I
got nothing from it.

As usual, thanks to everyone in advance!

Ronny Hamida

 

[David Billigmeier]

Yep, Array entered again:

=MEDIAN(IF(((LEFT('Sheet1'!$J$2:$J$5000,4)="1234")+(LEFT('Sheet1'!$J$2:$J$5000,4))="5678"),'Sheet1'!$A$2:$A$5000,""))

 

[Ronny Hamida]

It works! Thank you, Dave!

Ronny

Yep, Array entered again:

=MEDIAN(IF(((LEFT('Sheet1'!$J$2:$J$5000,4)="1234")+(LEFT('Sheet1'!$J$2:$J$5000,4))="5678"),'Sheet1'!$A$2:$A$5000,""))