ANOTHER SUMPRODUCT NON CONTIGUOS RANGE

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
SvgmassiveAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
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
 
FaustulusCommented:
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
 
SvgmassiveAuthor Commented:
hope this helps
Book2.xlsm
0
 
FaustulusCommented:
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
 
byundtCommented:
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
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.

All Courses

From novice to tech pro — start learning today.