Solved

SumProduct With Math Symbols

Posted on 2009-05-12
7
817 Views
Last Modified: 2012-05-06
Is there a way to use a validated cell to show math symbols to then be used in a Sumproduct formula.  It should be just a matter of getting the apostrophes straight, or so I am thinking.

I have attached a test spreadsheet showing what the answer should be if I just use the "<" and ">=" in the formula, but can not figure out the right way to use the symbols when in a cell.
Sumproduct-Criteria.xls
0
Comment
Question by:DougDodge
  • 4
  • 3
7 Comments
 
LVL 10

Expert Comment

by:Jon von der Heyden
ID: 24363938
You can't reference those 'symbols' indirectly.  You can however use a macro to write the formula for you.  Right click the sheet name > View Code and paste this into the Editor.  Close the editor again.  The formula should update each time you change a symbol or criteria.
Private Sub Worksheet_Change(ByVal Target As Range)
 

Dim strFormula As String
 

If Target.Address(0, 0) = "B2" Or Target.Address(0, 0) = "C2" Or _

    Target.Address(0, 0) = "E2" Or Target.Address(0, 0) = "F2" Then

    

    strFormula = "=SUMPRODUCT((Column1" & Range("B2") & Range("C2") & ")*(Column1" & Range("E2") & Range("F2") & "))"

    

    Range("H2").Formula = strFormula
 

End If
 

End Sub

Open in new window

0
 

Author Comment

by:DougDodge
ID: 24364293
This is a good solution, but can it be made to handle a range rather than a single cell?  Please have a look at the attached.
Sumproduct-Criteria-2.xls
0
 
LVL 10

Expert Comment

by:Jon von der Heyden
ID: 24364626
Try this;
Option Explicit
 

Private Sub Worksheet_Change(ByVal Target As Range)

 

Dim strFormula As String

Dim rngIsect As Range

Dim lngLastRow As Long
 

lngLastRow = Range("B" & Rows.Count).End(xlUp).Row
 

Set rngIsect = Union(Range("B2:C" & lngLastRow), Range("E2:F" & lngLastRow))
 

If Intersect(rngIsect, Target) Is Nothing Then Exit Sub

    

    strFormula = "=SUMPRODUCT((Column1" & Cells(Target.Row, "B") & Cells(Target.Row, "C") _

        & ")*(Column1" & Cells(Target.Row, "E") & Cells(Target.Row, "F") & "))"

    

    Cells(Target.Row, "H").Formula = strFormula

 

End Sub

Open in new window

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:DougDodge
ID: 24365555
The problem I face is how to adjust your coding for my application.  The range has to be restricted to F20 to I25 and the formula created must contain a double apostrophe in it.  This seems to be the issue now, as I can get your formula to work, but have an issue over the syntax of the apostrophes.  Please see attached.
SumproductTest2.xls
0
 
LVL 10

Expert Comment

by:Jon von der Heyden
ID: 24365930
The thing with quotes is that you need to wrap them in quotes to, see below:
Notice < ""Task Dependent"" >

Option Explicit
 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 

'Checks for Math Symbol or criteria changes and changes formulas.

Dim strFormula As String

Dim rngIsect As Range

Dim lngLastRow As Long

 

lngLastRow = Range("F" & Rows.Count).End(xlUp).Row

 

Set rngIsect = Union(Range("F20:G" & lngLastRow), Range("H20:I" & lngLastRow))

 

'SUMPRODUCT((Sched1E="Task Dependent")*(Sched1S>G21)*(Sched1S<=I21))
 

If Intersect(rngIsect, Target) Is Nothing Then Exit Sub

    strFormula = "=SUMPRODUCT((Sched1E=""Task Dependent"")*(Sched1S" & Cells(Target.Row, "F") & Cells(Target.Row, "G") & ")*(Sched1S" & Cells(Target.Row, "H") & Cells(Target.Row, "I") & "))"

    Cells(Target.Row, "L").Formula = strFormula

 

End Sub

Open in new window

0
 
LVL 10

Accepted Solution

by:
Jon von der Heyden earned 250 total points
ID: 24365966
Also, looking at your latest scenario rngIsect can be hardcoded:
Option Explicit
 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 

'Checks for Math Symbol or criteria changes and changes formulas.

Dim strFormula As String

Dim rngIsect As Range

 

Set rngIsect = Union(Range("F21:G24"), Range("H20:I25"))

 

'SUMPRODUCT((Sched1E="Task Dependent")*(Sched1S>G21)*(Sched1S<=I21))
 

If Intersect(rngIsect, Target) Is Nothing Then Exit Sub

    strFormula = "=SUMPRODUCT((Sched1E=""Task Dependent"")*(Sched1S" & Cells(Target.Row, "F") & Cells(Target.Row, "G") & ")*(Sched1S" & Cells(Target.Row, "H") & Cells(Target.Row, "I") & "))"

    Cells(Target.Row, "L").Formula = strFormula

 

End Sub

Open in new window

0
 

Author Closing Comment

by:DougDodge
ID: 31580502
Excellent, this worked perfectly.
Thanks....
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

706 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

19 Experts available now in Live!

Get 1:1 Help Now