Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a wild card in SUMPRODUCT
I'm using the following formula:
=SUMPRODUCT(--(D6:D1000="*/2-2 IN"),--(E6:E1000'="NBI")) which returns a value of "0". The "*" could be an A, B, C, D, HHC, or E. If I replace the * with one of the letters, I get a correct value. How can I use the wildcard returning a correct value for all the true conditions of A/2-2, B/2-2, C/2-2, D/2-2, E/2-2, and HHC/2-2? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a wild card in SUMPRODUCT
Hi,
The SUMPRODUCT function does not support wildcards. Second problem is that *anything will return anything. You could use something like this =SUMPRODUCT(--((D6:D1000="A/2-2 IN")+(D6:D1000="B/2-2 IN")+(D6:D1000="C/2-2 IN")+(D6:D1000="D/2-2 IN")+(D6:D1000="E/2-2 IN")+(D6:D1000="HHC/2-2 IN")0),--(E6:E1000="NBI")) -- If this helps, please click the Yes button Cheers, Shane Devenshire "SFC Traver" wrote: I'm using the following formula: =SUMPRODUCT(--(D6:D1000="*/2-2 IN"),--(E6:E1000'="NBI")) which returns a value of "0". The "*" could be an A, B, C, D, HHC, or E. If I replace the * with one of the letters, I get a correct value. How can I use the wildcard returning a correct value for all the true conditions of A/2-2, B/2-2, C/2-2, D/2-2, E/2-2, and HHC/2-2? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a wild card in SUMPRODUCT
Try this:
=SUMPRODUCT(--(ISNUMBER(SEARCH("/2-2 IN",D6:D1000))),--(E6:E1000="NBI")) Better to use cells to hold the criteria: D1 = /2-2 IN E1 = NBI =SUMPRODUCT(--(ISNUMBER(SEARCH(D1,D6:D1000))),--(E6:E1000=E1)) -- Biff Microsoft Excel MVP "SFC Traver" wrote in message ... I'm using the following formula: =SUMPRODUCT(--(D6:D1000="*/2-2 IN"),--(E6:E1000'="NBI")) which returns a value of "0". The "*" could be an A, B, C, D, HHC, or E. If I replace the * with one of the letters, I get a correct value. How can I use the wildcard returning a correct value for all the true conditions of A/2-2, B/2-2, C/2-2, D/2-2, E/2-2, and HHC/2-2? Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a wild card in SUMPRODUCT
Thanks, Shane. Works great. I was just hoping there was a less time consuming
way to do it. I even tried using the RIGHT function to no avail. But at least your solution works! "Shane Devenshire" wrote: Hi, The SUMPRODUCT function does not support wildcards. Second problem is that *anything will return anything. You could use something like this =SUMPRODUCT(--((D6:D1000="A/2-2 IN")+(D6:D1000="B/2-2 IN")+(D6:D1000="C/2-2 IN")+(D6:D1000="D/2-2 IN")+(D6:D1000="E/2-2 IN")+(D6:D1000="HHC/2-2 IN")0),--(E6:E1000="NBI")) -- If this helps, please click the Yes button Cheers, Shane Devenshire "SFC Traver" wrote: I'm using the following formula: =SUMPRODUCT(--(D6:D1000="*/2-2 IN"),--(E6:E1000'="NBI")) which returns a value of "0". The "*" could be an A, B, C, D, HHC, or E. If I replace the * with one of the letters, I get a correct value. How can I use the wildcard returning a correct value for all the true conditions of A/2-2, B/2-2, C/2-2, D/2-2, E/2-2, and HHC/2-2? Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a wild card in SUMPRODUCT
On Feb 16, 8:04*am, Shane Devenshire
wrote: Hi, The SUMPRODUCT function does not support wildcards. *Second problem is that *anything will return anything. You could use something like this =SUMPRODUCT(--((D6:D1000="A/2-2 IN")+(D6:D1000="B/2-2 IN")+(D6:D1000="C/2-2 IN")+(D6:D1000="D/2-2 IN")+(D6:D1000="E/2-2 IN")+(D6:D1000="HHC/2-2 IN")0),--(E6:E1000="NBI")) Is there a reason to put 0. Traver's original question doesn't have this criteria. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a wild card in SUMPRODUCT
No. It's not needed.
Since a cell can only meet one *or* the other conditions the resulting array will only return a 1 or 0 so a test for 0 is superfluous. -- Biff Microsoft Excel MVP "Rasoul Khoshravan" wrote in message ... On Feb 16, 8:04 am, Shane Devenshire wrote: Hi, The SUMPRODUCT function does not support wildcards. Second problem is that *anything will return anything. You could use something like this =SUMPRODUCT(--((D6:D1000="A/2-2 IN")+(D6:D1000="B/2-2 IN")+(D6:D1000="C/2-2 IN")+(D6:D1000="D/2-2 IN")+(D6:D1000="E/2-2 IN")+(D6:D1000="HHC/2-2 IN")0),--(E6:E1000="NBI")) Is there a reason to put 0. Traver's original question doesn't have this criteria. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a wild card in SUMPRODUCT
But it gives me a feeling of safety.
If I gave that formula to someone who decided to include an additional "or" check in a different column, I'd want that "0" there. =SUMPRODUCT(--((D6:D1000="A/2-2 IN")+(E6:E1000="something else")+.... Rasoul Khoshravan wrote: On Feb 16, 8:04 am, Shane Devenshire wrote: Hi, The SUMPRODUCT function does not support wildcards. Second problem is that *anything will return anything. You could use something like this =SUMPRODUCT(--((D6:D1000="A/2-2 IN")+(D6:D1000="B/2-2 IN")+(D6:D1000="C/2-2 IN")+(D6:D1000="D/2-2 IN")+(D6:D1000="E/2-2 IN")+(D6:D1000="HHC/2-2 IN")0),--(E6:E1000="NBI")) Is there a reason to put 0. Traver's original question doesn't have this criteria. -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a wild card in SUMPRODUCT
Try this:
=SUMPRODUCT((RIGHT(D6:D1000,7)="/2-2 IN")*(E6:E1000="NBI")) "SFC Traver" wrote: I'm using the following formula: =SUMPRODUCT(--(D6:D1000="*/2-2 IN"),--(E6:E1000'="NBI")) which returns a value of "0". The "*" could be an A, B, C, D, HHC, or E. If I replace the * with one of the letters, I get a correct value. How can I use the wildcard returning a correct value for all the true conditions of A/2-2, B/2-2, C/2-2, D/2-2, E/2-2, and HHC/2-2? Thanks! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a wild card in SUMPRODUCT
=SUMPRODUCT(--((D6:D1000="A/2-2 IN")+(E6:E1000="something else")+....
Yes, you would need it when testing different arrays for an "or" condition. The OP is testing the same array for the "or" condition. For the OP, here's how it works... Let's assume you want to test A1 to see if it contains either an A or a B. A1 can contain only one or the other, it can't can't contain both! Let's assume A1 = A (A1="A")+(A1="B") (A1="A") = TRUE (A1="B") = FALSE TRUE + FALSE = 1 Now, let's assume A1 = B (A1="A") = FALSE (A1="B") = TRUE FALSE + TRUE = 1 The result of the test can only be 1 or 0. So, in this case, testing for 0 --((A1="A")+(A1="B")0) Is redundant since it will return the exact same result. (A1="A")+(A1="B") = 1 --((A1="A")+(A1="B")0) = 1 Now, if you're testing different arrays for an "or" condition... A1 or B1 = A Let's assume both A1 and B1 = A (A1="A")+(B1="A") A1 = "A" = TRUE B1 = "A" = TRUE TRUE + TRUE = 2 Since this is an "or" condition you don't want the result to be counted twice and that's where the test for 0 comes into play. --((A1="A")+(B1="A")0) = 1 Hopefully, that will shed some light on it. -- Biff Microsoft Excel MVP "Dave Peterson" wrote in message ... But it gives me a feeling of safety. If I gave that formula to someone who decided to include an additional "or" check in a different column, I'd want that "0" there. =SUMPRODUCT(--((D6:D1000="A/2-2 IN")+(E6:E1000="something else")+.... Rasoul Khoshravan wrote: On Feb 16, 8:04 am, Shane Devenshire wrote: Hi, The SUMPRODUCT function does not support wildcards. Second problem is that *anything will return anything. You could use something like this =SUMPRODUCT(--((D6:D1000="A/2-2 IN")+(D6:D1000="B/2-2 IN")+(D6:D1000="C/2-2 IN")+(D6:D1000="D/2-2 IN")+(D6:D1000="E/2-2 IN")+(D6:D1000="HHC/2-2 IN")0),--(E6:E1000="NBI")) Is there a reason to put 0. Traver's original question doesn't have this criteria. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I use wild card in SUMPRODUCT formula? | Excel Worksheet Functions | |||
wild card in sumproduct | Excel Worksheet Functions | |||
sumproduct with a search and wild card | Excel Discussion (Misc queries) | |||
Wild Card Search | Excel Worksheet Functions | |||
Wild card * | Excel Worksheet Functions |