Solved

SUMPRODUCT NON CONTIGUOUS RANGE

Posted on 2013-06-29
6
370 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
  • 4
  • 2
6 Comments
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Svgmassive
Comment Utility
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 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
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
Comment Utility
thanks byundt awesome
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now