Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sumproduct with Wildcard *
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 |
#2
|
|||
|
|||
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))) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Jim" wrote in message ... 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 |
#3
|
|||
|
|||
Why not just: =SUMPRODUCT((B2:B4=B6)*(C2:C4=C6))
Or: =SUMPRODUCT((A2:A4<"")*(B2:B4=B6)*(C2:C4=C6)) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Jim" wrote in message ... 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 |
#4
|
|||
|
|||
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? "JulieD" wrote: 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))) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Jim" wrote in message ... 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 |
#5
|
|||
|
|||
My objective is to make this more dynamic. I may have the need to put the *
in one or more of the columns. "Max" wrote: Why not just: =SUMPRODUCT((B2:B4=B6)*(C2:C4=C6)) Or: =SUMPRODUCT((A2:A4<"")*(B2:B4=B6)*(C2:C4=C6)) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Jim" wrote in message ... 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 |
#6
|
|||
|
|||
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) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Jim" wrote in message ... 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? "JulieD" wrote: 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))) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Jim" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |