SumProduct With Math Symbols

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
DougDodgeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jon von der HeydenConnect With a Mentor Course Leader & Managing DirectorCommented:
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
 
Jon von der HeydenCourse Leader & Managing DirectorCommented:
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
 
DougDodgeAuthor Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
Jon von der HeydenCourse Leader & Managing DirectorCommented:
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
 
DougDodgeAuthor Commented:
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
 
Jon von der HeydenCourse Leader & Managing DirectorCommented:
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
 
DougDodgeAuthor Commented:
Excellent, this worked perfectly.
Thanks....
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.