Sumproduct with Wildcard *

[Guest]

I would like to use a wildcard character with a sumproduct formula.
Data
1 A B C
2 Joe CA 01
3 Sue MA 01
4 Dan CA 02

6 * CA 01
=Sumproduct((A2:A4=A6)*(B2:B4=B6)*(C2:C4=C6))
With "*" in cell A6 I expect to get a result of 1 but instead I get 0. Any
suggestions?
Thanks

 

[JulieD]

Hi Jim

you can't use wildcards with SUMPRODUCT ... so the best solution which i
think (and i'm sure someone will correct me if i'm wrong) will give you max
flexibility is:

=IF(A6="*",SUMPRODUCT(--(B1:B3=B6),--(C1:C3=C6)),SUMPRODUCT(--(A1:A3=A6),--(B1:B3=B6),--(C1:C3=C6)))

 

[Max]

Why not just: =SUMPRODUCT((B2:B4=B6)*(C2:C4=C6))

Or: =SUMPRODUCT((A2:A4<>"")*(B2:B4=B6)*(C2:C4=C6))

 

[Guest]

Thanks Julie. Your suggestion looks good but I was hoping to be able to
evaluate a much larger number of criteria and to use "*" in any number of the
fields as needed. Writing a formula to account for all the possiblities would
be difficult.

Any other suggestions?

 

[Guest]

My objective is to make this more dynamic. I may have the need to put the *
in one or more of the columns.

 

[JulieD]

Hi Jim

you can use code to create the appropriate formula ... you might like to
link this code to a button which says "Calc Results" or something similar
.... i've done it based on your example of three inputs but you could easily
add more ... (btw if you're lucky, someone else on here might rewrite this
code to make it more efficient)

---
Sub multisumproduct()

Dim input1
Dim input2
Dim input3
Dim rng1 As String
Dim rng2 As String
Dim rng3 As String
Dim strformula As String

On Error GoTo err_handler

input1 = Sheets("Sheet7").Range("A6")
input2 = Sheets("Sheet7").Range("B6")
input3 = Sheets("Sheet7").Range("C6")

rng1 = "A1:A3"
rng2 = "B1:B3"
rng3 = "C1:C3"
strformula = strformula & "=SUMPRODUCT("

If input1 <> "*" Then
If Val(input1) = 0 And Len(input1) > 1 Then
strformula = strformula & "--(" & rng1 & "=""" & input1 & """),"
Else
strformula = strformula & "--(" & rng1 & "=" & input1 & "),"
End If
End If

If input2 <> "*" Then
If Val(input2) = 0 And Len(input2) > 1 Then
strformula = strformula & "--(" & rng2 & "=""" & input2 & """),"
Else
strformula = strformula & "--(" & rng2 & "=" & input2 & "),"
End If
End If

If input3 <> "*" Then
If Val(input3) = 0 And Len(input3) > 1 Then
strformula = strformula & "--(" & rng3 & "=""" & input3 & """),"
Else
strformula = strformula & "--(" & rng3 & "=" & input3 & "),"
End If
End If

strformula = Left(strformula, Len(strformula) - 1) & ")"
Range("D6").Formula = strformula
Exit Sub
err_handler:
MsgBox "Can't be done"

End Sub
--

the code can go in a normal module (right mouse click on the sheet tab,
choose view code, choose insert / module - copy & paste the code in there -
use ALT &F11 to get back to your workbook and run it via, tools / macro /
macros - mulitsumproduct - RUN)