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

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
0
Svgmassive
Asked:
Svgmassive
  • 2
  • 2
1 Solution
 
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
 
byundtCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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