Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SUMPRODUCT NON CONTIGUOUS RANGE

Posted on 2013-06-29
6
Medium Priority
?
429 Views
Last Modified: 2013-06-29
I need help getting this function to work or an alternative
=SUMPRODUCT(--SUBSTITUTE(TEXT($O$12,$O$16,"0.00"),".",":"))

thanks
0
Comment
Question by:Svgmassive
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39286877
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
 
LVL 81

Expert Comment

by:byundt
ID: 39286885
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
 
LVL 81

Expert Comment

by:byundt
ID: 39286903
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Svgmassive
ID: 39286987
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
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 39287044
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
 

Author Closing Comment

by:Svgmassive
ID: 39287281
thanks byundt awesome
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question