Solved

ANOTHER SUMPRODUCT NON CONTIGUOS RANGE

Posted on 2013-06-29
5
208 Views
Last Modified: 2013-07-11
I just got this from the good folks at e.e it work for the previous formula i need to have it adapted to the formula listed below.

one for singe cell/range
and one for multiranges

=SUMPRODUCT(--(SUBSTITUTE(TEXT(INDEX((O$5:O$7,O$9:O$10),,,1),"0.00"),".",":")+SUBSTITUTE(TEXT(INDEX((O$5:O$7,O$9:O$10),,,2),"0.00"),".",":")))
thanks
0
Comment
Question by:Svgmassive
  • 2
  • 2
5 Comments
 
LVL 14

Expert Comment

by:Faustulus
ID: 39287687
Hmmm!
You might use a named range to index non-contiguous ranges. I would need to know what you want to do in order to provide a solution. Best, post a workbook where you have examples to better explain by. You might add a link to the previous question, too.
0
 

Author Comment

by:Svgmassive
ID: 39287974
hope this helps
Book2.xlsm
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39288111
No.
If you don't have the time to explain what you want, I won't have the time to guess at your needs.
I hope this helps.
0
 
LVL 81

Expert Comment

by:byundt
ID: 39288914
Because you have three rows in one range and two in the other, you can't use the INDEX approach I had suggested in another thread. Instead, for the sum of hours where row 4 does not contain "rem"
=SUMPRODUCT(ISERR(SEARCH("rem",O4:V4))*SUBSTITUTE(TEXT(O5:V7,"0.00"),".",":"))+SUMPRODUCT(ISERR(SEARCH("rem",O4:V4))*SUBSTITUTE(TEXT(O9:V10,"0.00"),".",":"))

And for the sum of hours where row 4 does contain "rem"
=SUMPRODUCT(ISNUMBER(SEARCH("rem",O4:V4))*SUBSTITUTE(TEXT(O5:V7,"0.00"),".",":"))+SUMPRODUCT(ISNUMBER(SEARCH("rem",O4:V4))*SUBSTITUTE(TEXT(O9:V10,"0.00"),".",":"))

The above formulas add up your data and return an answer in "days". If you want the result in "hours", then you should either format the result using Custom format [h]:mm or else multiply by 24.


Rather than use formulas that are confusing even for Experts in this forum, why not enter your data as time? I've put a macro in the "Better way" worksheet in the attached workbook that will convert a number like 1.30 into a time like 1:30. You can then use very simple SUM and SUMIF formulas to add up your times.

The macro must go on the worksheet code pane, and won't work at all if installed anywhere else. You must enable macros. As written, the macro watches cells O5:V7 and O9:V10 for data input. If there is a decimal point in the input, then the data is converted into a time.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, targ As Range
Set targ = Range("O5:V7", "O9:V10")
Set targ = Intersect(targ, Target)
If targ Is Nothing Then Exit Sub

Application.EnableEvents = False
For Each cel In targ.Cells
    If InStr(1, cel.Text, ".") > 0 Then
        cel.NumberFormat = "[h]:mm"
        cel.Value = TimeSerial(Int(cel.Value), 100 * (cel.Value - Int(cel.Value)), 0)
    End If
Next
Application.EnableEvents = True
End Sub

Open in new window

TimeReportQ28171654.xlsm
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39288926
An alternate macro in the "Better way (alt)" worksheet code pane checks for a colon in the cell value. If one is present, then the data must be entered in time format. If one is not present, then the data you enter is converted into time format. Unlike the previous version, you do not need to enter a decimal point for whole hours. If you enter a 0 (or a blank), then the original 00.00 number format is restored. And if you enter text, the routine accepts it without a run-time error.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, targ As Range
Set targ = Range("O5:V7", "O9:V10")
Set targ = Intersect(targ, Target)
If targ Is Nothing Then Exit Sub

Application.EnableEvents = False
For Each cel In targ.Cells
    If cel.Value = 0 Then
        cel.NumberFormat = "00.00"
        cel.ClearContents
    ElseIf InStr(1, cel.Text, ":") = 0 Then
        If IsNumeric(cel.Value) Then
            cel.NumberFormat = "[h]:mm"
            cel.Value = TimeSerial(Int(cel.Value), 100 * (cel.Value - Int(cel.Value)), 0)
        End If
    End If
Next
Application.EnableEvents = True
End Sub

Open in new window

TimeReportQ28171654.xlsm
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
.Range Set 25 75
Excel 2010 - Column Heading Formating 6 14
Excel VBA - macro in one workbook with another open 4 19
Select Next Route by Time 4 19
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

813 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

17 Experts available now in Live!

Get 1:1 Help Now