Solved

ANOTHER SUMPRODUCT NON CONTIGUOS RANGE

Posted on 2013-06-29
5
201 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel 2016 Not Responding Issues 6 29
Excel macro runs twice 13 52
excel help 4 20
min values in series 13 14
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

863 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

20 Experts available now in Live!

Get 1:1 Help Now