• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Non-contiguous Ranges :: Sum/Index/Match

G

Gamma48

Member
I have a sum/index/match formula that works properly without blanks cells in the range, but cannot figure out how to manipulate the formula to also accept non-contiguous ranges. I know it would be much easier to just remove any blank cells, but some people using the spreadsheet will leave blanks throughout the range for their benefit.

I've attached a simple sheet that hopefully will clarify the issue.

Thanks.
 

Attachments

Rather than looking at col C trying to find blanks, can we look at col B for the different blocks?
=IF(B3="","",SUM(C3:INDEX(C4:C$100,IFERROR(MATCH("*",B4:B$100,0),ROWS(B4:B$100)))))
 
I do believe it is near spot on; made a couple tweaks--moved the beginning of the sum down one row and added an offset, to account for any number that might be entered in the cell between the heading and calculated total. I have a macro running on that cell which I left out in the above example.

=IF(B3="","",SUM(C4:INDEX(C4:C$100,IFERROR(MATCH("*",OFFSET(B4:B$100,1,0),0),ROWS(B4:B$100)))))

Do you know of a better way to ignore that cell? I haven't been able to get it to bonk out yet with my changes...
 
I'm afraid I don't understand...does your formula work, or is there still a problem? Or is there another cell we are trying to ignore? :(
 
Oh no, it works just fine. Just wasn't sure if that was the best method with an offset and possible miscalculations. Thanks again for the help.
 
Ah. I might suggest getting rid of OFFSET simply because it's a volatile function and you have a constant offset of 1. Those volatile functions can get annoying, especially when you start having a lot of them.
=IF(B3="","",SUM(C4:INDEX(C4:C$100,IFERROR(MATCH("*",B5:B$101,0),ROWS(B4:B$100)))))
 
That makes sense, good deal. I'm in that territory you reference in your sig...that's why I asked. I can make it "work" but rarely by the best route.
 
You must log in or register to reply here.
Back
Top