Forum Discussion
SUMIF, but with a strange range
I've got a spreadsheet that looks like this (faked data for this):
The formula from column D is in the function bar. I'm basically taking the batch size, dividing it by the total number of parts in the container (which I get from summing up all of the batches), and then multiplying that to get the portion of the total container price for that batch. You can see that the total cost for each container I always have in the first row for each container; I just leave the rest of them blank so that the total container price is always the sum of the first price plus a bunch of zeros.
The container number is completely arbitrary: I don't really have container numbers; I'm just using them so I can have a criteria for SUMIF. What I'd really like to do is get rid of that column and just say that the range is from the first non-blank container cost to one less than the next non-blank container cost. If I needed to, I'd be willing to color all of the rows for each container to mark the range. Anything where I can get rid of column A but still be able to figure out the total number of items in the container and the percent of the cost for each batch. Any Excel magicians out there with ideas about how to do that?
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.
2 Replies
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.
