Forum Discussion
SUMIF, but with a strange range
peteresnick Honestly yes it CAN be done but IMHO your solution is much cleaner. If you don't want to look at column A just hide it. If you are manually typing the values in A then you could use a simple equation like =Count($C$2:$C2) into A2 and fill down. If you still insist here is 1 option:
=LET(BinRng,INDEX($A:$A,MAX(ROW($B$1:$B1)*($B$1:$B1>0))):INDEX($B1:$B1000,IFERROR(XMATCH(1,--($B2:$B1000>0),1,1),MAX(ROW($A1:$A1000)*($A1:$A1000>0))-ROW($A1)+1)), A1/SUM(INDEX(BinRng,,1))*SUM(INDEX(BinRng,,2)))This does assume that no 'bin' will have more than 1000 rows. I tried to create a single spill formula but that had array of arrays issues that I chose to avoid.
peteresnick Honestly yes it CAN be done but IMHO your solution is much cleaner. If you don't want to look at column A just hide it. If you are manually typing the values in A then you could use a simple equation like =Count($C$2:$C2) into A2 and fill down. If you still insist here is 1 option:
=LET(BinRng,INDEX($A:$A,MAX(ROW($B$1:$B1)*($B$1:$B1>0))):INDEX($B1:$B1000,IFERROR(XMATCH(1,--($B2:$B1000>0),1,1),MAX(ROW($A1:$A1000)*($A1:$A1000>0))-ROW($A1)+1)),
A1/SUM(INDEX(BinRng,,1))*SUM(INDEX(BinRng,,2)))This does assume that no 'bin' will have more than 1000 rows. I tried to create a single spill formula but that had array of arrays issues that I chose to avoid.
- Excellent! Not only addressed my issue, but gave me a zillion new ideas on how to do this. Thanks.
