The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!
Is there an option that can handle one or more ranges?Could you please explain the real problem you are trying to solve? String functions like SUBSTITUTE are less flexible than you might wish in array formulas such as SUMPRODUCT.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
INDEX(($O$12,$O$16,,,1) returns a reference to the first area ($O$12)
INDEX(($O$12,$O$16,,,2) returns a reference to the second area ($O$16)
You can use this method in a SUMPRODUCT as:
=SUMPRODUCT(--SUBSTITUTE(T
The same approach will also work in a SUM formula with regular entry:
=SUM(--SUBSTITUTE(TEXT(IND
This approach is admittedly not very impressive for a non-contiguous range of two cells. It gets a lot more interesting if you expand it to non-contiguous columns of cells, such as in this thread:
http://www.tek-tips.com/viewthread.cfm?qid=1710405
Brad