Use TEXT to extract the TRUE SUMIFS range from a multiple SUMIFS cell

[Silverjman]

I have the following formula.

=IF(N$2>ActualsEndDate, IF(ReturnsBasis="Cashflow",SUMIFS(Units!YL$10:YL$320,Units!YL$10:YL$320,">"&0,Units!$C$10:$C$320,$C78), IF(ReturnsBasis="Income",SUMIFS(Units!DBN$10:DBN$320,Units!DBN$10:DBN$320,">"&0,Units!$C$10:$C$320,$C78),0)),    SUMIFS(Assets!BI$10:BI$60,Assets!$C$10:$C$60,$C78))

I am desperate to create a doubleclick drilldown in vba, BUT I thought as a first, VERY crude, step I would rip the correct range out of the formula and display in as text in a cell via a formula.

To which end I thought I would append TEXT to each option in a seperate but mirroring cell.

=IF(N$2>ActualsEndDate, IF(ReturnsBasis="Cashflow",TEXT(SUMIFS(Units!YL$10:YL$320,Units!YL$10:YL$320,">"&0,Units!$C$10:$C$320,$C78),????), IF(ReturnsBasis="Income",TEXT(SUMIFS(Units!DBN$10:DBN$320,Units!DBN$10:DBN$320,">"&0,Units!$C$10:$C$320,$C78),????),0)),    TEXT(SUMIFS(Assets!BI$10:BI$60,Assets!$C$10:$C$60,$C78),????))

=IF(N$2>ActualsEndDate, IF(ReturnsBasis="Cashflow",,TEXT(SUMIFS(Units!YL$10:YL$320,Units!YL$10:YL$320,">"&0,Units!$C$10:$C$320,$C78),????), IF(ReturnsBasis="Income",,TEXT(SUMIFS(Units!DBN$10:DBN$320,Units!DBN$10:DBN$320,">"&0,Units!$C$10:$C$320,$C78),,????),0)), ,TEXT(SUMIFS(Assets!BI$10:BI$60,Assets!$C$10:$C$60,$C78),,????))[/

BUT I am clueless as to what to put in for the format "????", am I asking TEXT to do something it can't do? Tried to find a "general" or "text" format code.

THANKS for reading!

p.s. once I had the correct SUMIFS I would propose to use MID/LEFT to extract the sum range and then use indirect to INDEXMATCH to jump to the first appearence of the ID's in the appropriate sum range.

 

[Silverjman]

please ignore the extra red commas in RED