List of values used in SUMIFS

[BullyHoddy]

Hi all,
I have a lot of SUMIFS statements set up in my workbook. What I would like is to be able to set up some formula that allows me to specify which of these statements to look at and then to list all the values that are being summed to get that result. I want to see the list so that I can then get its median. Any ideas how to do this? Thanks.

 

[Jeffrey Mahoney]

Please show your sumifs formula

You can use a similar formula using an array formula.
=MEDIAN(IF(E11:E18>2,E11:E18))
YOU MUST FINISH THE ENTRY BY PRESSING CTRL SHIFT ENTER

 

[BullyHoddy]

Thanks for the reply Jeffrey. They're pretty messy formulae- here's an example with named ranges etc.: =SUMIFS(PnLSumRange,LongDatedProj!D:D,'PnL Breakdown'!N22,LongDatedProj!CD:CD,"=1",LongDatedProj!DU:DU,">"&-1,PnLSumRange,"<"&$A$5,PnLSumRange, ">"&-$A$5,LongDatedProj!L:L,">="&'PnL Breakdown'!$A$8,LongDatedProj!L:L,"<="&'PnL Breakdown'!$A$11)

I don't think the array formula will work since I have so many different Sumifs in the sheet. What I'm really looking for is something that just shows me all the values that fulfill all of the "ifs" criteria which I can adjust without actually rewriting the full formula every time. So say I have sumifs in all cells from A1:A200, a function of the form List(A1) which then lists out all the elements of this sumif formula and can easily be adapted to reference other cells (e.g. List(A54)) would be ideal..

 

[Jeffrey Mahoney]

This is what I discovered. You can use nested IF statements in an array formula. I did a simple one:
=MEDIAN(IF(E10:E17>3,IF(E10:E17<15,E10:E17)))
Confirmed with Control Shift Enter

The key is to adding criteria until the you reach the last nested if, in which case you supply the full range again in blue.

Your formula might look like this. I can't confirm because I don't have your data. Also, the red part of the formula is where you put the range you want the median result. This is just an idea. I'm not as good with the Array formulas. Search for "median with multiple IF"

=Median(if(LongDatedProj!D:D='PnL Breakdown'!N22,if(LongDatedProj!CD:CD=1,if(LongDatedProj!DU:DU>-1,if(PnLSumRange<$A$5,if(PnLSumRange >-$A$5,if(LongDatedProj!L:L>='PnL Breakdown'!$A$8,if(LongDatedProj!L:L<='PnL Breakdown'!$A$11,LongDatedProj!D:D))))))))