Solved

ANOTHER SUMPRODUCT NON CONTIGUOS RANGE

Posted on 2013-06-29
5
225 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

739 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