# Medium with sumif formula help

#### alloakland1

##### New Member
Hi, I have the following array formula, but it is not working. Any ideas would be much appreciated!
I press crtl,shift,enter but a #NUM error shows up.

=MEDIAN(IF('Sheet2'!A1:A5000=(CONCATENATE(a1,d2)),'Sheet3'!\$BF1:\$BF5000))/60

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
You haven't completed the IF statement, it has an expression for TRUE, but not FALSE so when it evaluates FALSE it will return FALSE which MEDIAN can't handle. I'm not sure that even putting a ",0' at the end of the if will help as the zeros will still be treated as members of the set for the median.

If it is a one off I would Autofilter the data to only show the condition then copy and paste the visible data to a new location to get the median.

Hi, I have the following array formula, but it is not working. Any ideas would be much appreciated!
I press crtl,shift,enter but a #NUM error shows up.

=MEDIAN(IF('Sheet2'!A1:A5000=(CONCATENATE(a1,d2)),'Sheet3'!\$BF1:\$BF5000))/60

It looks to me that you have too many parenthesis. You might try this version. You shouldn't need the false arguement in the IF statement because MEDIAN will ignore false returns in an array. {=MEDIAN(IF('Sheet2'!A1:A5000=CONCATENATE(a1,d2),'Sheet3'!\$BF1:\$BF5000))/60}

CTL+SHIFT+ENTER the formula.

Hi, thank you for your help.

I put in this formula {=MEDIAN(IF('Sheet2'!A1:A5000=CONCATENATE(a1,d2),'Sheet3'!\$BF1:\$BF5000))/60}, CTL+SHIFT+ENTER. This still gave me the #num error. any other ideas? I really appreciate your help. This is all new to me

Remove the quotes ' from the sheet reference. It should look like this:
=MEDIAN(IF(Sheet2!A1:A5000=CONCATENATE(a1,d2),Sheet3!\$BF1:\$BF5000))/60

If that doesn't work then walk through the formula using the formula evaluator. ALT+T+U+F

...because MEDIAN will ignore false returns in an array.

A quick check shows MEIDAN doesn't ignore false, it treats it as a zero which will still skew the result of the formula after you get it functional.

A quick check shows MEIDAN doesn't ignore false, it treats it as a zero which will still skew the result of the formula after you get it functional.

But it does.

Hi, thank you for your help.

I put in this formula {=MEDIAN(IF('Sheet2'!A1:A5000=CONCATENATE(a1,d2),'Sheet3'!\$BF1:\$BF5000))/60}, CTL+SHIFT+ENTER. This still gave me the #num error. any other ideas? I really appreciate your help. This is all new to me

A different point. How do you "know" Sheet2!A1:A5000 is associated with Sheet3!\$BF1:\$BF5000? That is, why are they on different sheets?

Aladin, point taken. When tested I passed arguments directly but after your post I double checked by reference and found something strange; when you put the arguments directly into the formula the FALSE isn't ignored, only when they are referenced.
i.e. =MEDIAN(FALSE,FALSE,FALSE,1,2,3) give 0.5 while =MEDIAN(C1:C6) referencing the same values gives 2 (in XL2003).

I confirmed on MSDN that this is expected but it is certainly not intuitive.

A quick check shows MEIDAN doesn't ignore false, it treats it as a zero which will still skew the result of the formula after you get it functional.
This is true if the formula is not array entered. If you CSE the formula, it ignores all the FALSE values; at least in my attempts. It won't treat it as zero.

For example: =MEDIAN({FALSE,FALSE,FALSE,1,2,3}) gives 2
=MEDIAN(FALSE,FALSE,FALSE,1,2,3) gives 0.5

Last edited:

Replies
4
Views
79
Replies
14
Views
946
Replies
1
Views
287
Replies
5
Views
355
Replies
20
Views
1K

1,214,774
Messages
6,121,495
Members
449,034
Latest member
Raygers

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back