• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

SUMPRODUCT NON CONTIGUOUS RANGE

I need help getting this function to work or an alternative
=SUMPRODUCT(--SUBSTITUTE(TEXT($O$12,$O$16,"0.00"),".",":"))

thanks
0
Svgmassive
Asked:
Svgmassive
  • 4
  • 2
1 Solution
 
byundtCommented:
If you put the two cells inside parentheses, you create a union of them. You can then refer to different areas within that union using INDEX function:
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(TEXT(INDEX(($O$12,$O$16),,,1)+INDEX(($O$12,$O$16),,,2),"0.00"),".",":"))

The same approach will also work in a SUM formula with regular entry:
=SUM(--SUBSTITUTE(TEXT(INDEX(($O$12,$O$16),,,1)+INDEX(($O$12,$O$16),,,2),"0.00"),".",":"))

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
0
 
byundtCommented:
My previous suggestion was incorrect. It did the calculations in the wrong order--first adding the numbers, then performing the text manipulation.

Try either of the following instead:
=100*(MROUND($O$12,0.01)+MROUND($O$16,0.01))/1440
=(MROUND($O$12,0.01)+MROUND($O$16,0.01))/14.4
0
 
byundtCommented:
With the correct ordering of operations, my first suggestion should have been:
=SUMPRODUCT(--(SUBSTITUTE(TEXT(INDEX(($O$12,$O$16),,,1),"0.00"),".",":")+SUBSTITUTE(TEXT(INDEX(($O$12,$O$16),,,2),"0.00"),".",":")))

For a two-cell range, the MROUND approach is much simpler.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
SvgmassiveAuthor Commented:
excellent.just another question if it's only one range as in
=SUMPRODUCT(--SUBSTITUTE(TEXT($O$12,"0.00"),".",":"))
is there an option that can handle one or more ranges.
0
 
byundtCommented:
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.

I have posted two formulas that can be expanded to one or more ranges:
=(MROUND($O$12,0.01)+MROUND($O$16,0.01))/14.4       good for single-cell ranges
=SUMPRODUCT(--(SUBSTITUTE(TEXT(INDEX(($O$12,$O$16),,,1),"0.00"),".",":")+SUBSTITUTE(TEXT(INDEX(($O$12,$O$16),,,2),"0.00"),".",":")))      good for multi-cell ranges

It may be, however, that your real problem is best solved using a different approach.
0
 
SvgmassiveAuthor Commented:
thanks byundt awesome
0
Question has a verified solution.

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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now