Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wildcard in SUM
I'm hoping someone will add to be able to show me what I'm doing wrong. I'll
try to show a simple example. Spreadsheet view: A B C 1 11COH1092069/15 CPO 14 2 11COH1092069/15 14 3 11COH1092069/15 CPO 14 4 11COH1092069/14 14 Formula that I would like to use: =(SUM(B1:B4="CPO")*(A1:A4="*2069/15*")*C1:C4) It doesn't work, any ideas? Thx - Dean |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wildcard in SUM
Give SumProduct a try...
=SUMPRODUCT(--(ISNUMBER(FIND("2069/15", $A$1:$A$4))), --($B$1:$B$4 = "CPO"),$C$1:$C$4) Take a look at this for an explanation (Example 9)... http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "Dean" wrote: I'm hoping someone will add to be able to show me what I'm doing wrong. I'll try to show a simple example. Spreadsheet view: A B C 1 11COH1092069/15 CPO 14 2 11COH1092069/15 14 3 11COH1092069/15 CPO 14 4 11COH1092069/14 14 Formula that I would like to use: =(SUM(B1:B4="CPO")*(A1:A4="*2069/15*")*C1:C4) It doesn't work, any ideas? Thx - Dean |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wildcard in SUM
On Jul 25, 4:02 pm, Dean wrote:
I'm hoping someone will add to be able to show me what I'm doing wrong. I'll try to show a simple example. Spreadsheet view: A B C 1 11COH1092069/15 CPO 14 2 11COH1092069/15 14 3 11COH1092069/15 CPO 14 4 11COH1092069/14 14 Formula that I would like to use: =(SUM(B1:B4="CPO")*(A1:A4="*2069/15*")*C1:C4) It doesn't work, any ideas? Thx - Dean I don't think I understand what you are trying to do with the 'SUM(B1:B4="CPO")' statement. It looks like you want to count the number of times that "CPO" shows up in the range. If this is the case, you should use 'Countif(B1:B4,"CPO")' instead. As for the second expression '(A1:A4="*2069/15*")' it looks like you want to use the same type of thing, but I am unsure about the wildcards being correct. I think you will need to use some sort of search or parse to see if the value has that particular string and then count it. A bit more complex but not impossible. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wildcard in SUM
Dean,
here's one way. =SUM((B1:B4="CPO")*(IF(ISERROR(FIND("2069/15", A1:A4)), FALSE, TRUE))*C1:C4) -- Hope that helps. Vergel Adriano "Dean" wrote: I'm hoping someone will add to be able to show me what I'm doing wrong. I'll try to show a simple example. Spreadsheet view: A B C 1 11COH1092069/15 CPO 14 2 11COH1092069/15 14 3 11COH1092069/15 CPO 14 4 11COH1092069/14 14 Formula that I would like to use: =(SUM(B1:B4="CPO")*(A1:A4="*2069/15*")*C1:C4) It doesn't work, any ideas? Thx - Dean |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wildcard in SUM
Dean,
Assuming you are trying to sum the corresponding cells that end in 2069/15 in the first column and also have CPO in the second column you need to do the following: =RIGHT(TRIM(A1),7)&B1 in column (copy down) the formula to sum the relevant cells is then =SUMIF(D1:D4,"2069/15CPO",C1:C4) HTH Toyin. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wildcard in SUM
Vergel,
Your formula works terrific as an array. I have a question, I've read in some of the other questions in this site, that SUMPRODUCT has some limitations on how many rows of data that it will handle accurately and that SUM doesn't have that limitation, in your estimation is that true? Possilby Jim(1st responder) has an opinion on this as well? Thanks for your response, I really appreciate it. Dean "Vergel Adriano" wrote: Dean, here's one way. =SUM((B1:B4="CPO")*(IF(ISERROR(FIND("2069/15", A1:A4)), FALSE, TRUE))*C1:C4) -- Hope that helps. Vergel Adriano "Dean" wrote: I'm hoping someone will add to be able to show me what I'm doing wrong. I'll try to show a simple example. Spreadsheet view: A B C 1 11COH1092069/15 CPO 14 2 11COH1092069/15 14 3 11COH1092069/15 CPO 14 4 11COH1092069/14 14 Formula that I would like to use: =(SUM(B1:B4="CPO")*(A1:A4="*2069/15*")*C1:C4) It doesn't work, any ideas? Thx - Dean |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wildcard in SUM
I have received a couple of really great responses that work. I was
trying to get the quantity total, based on the conditions mentioned of searching for CPO in Column B, then searching for a portion of the string 2069/15 in Column A. But anyway, looks like I'm good now. Thanks for your response. Dean "theSquirrel" wrote: On Jul 25, 4:02 pm, Dean wrote: I'm hoping someone will add to be able to show me what I'm doing wrong. I'll try to show a simple example. Spreadsheet view: A B C 1 11COH1092069/15 CPO 14 2 11COH1092069/15 14 3 11COH1092069/15 CPO 14 4 11COH1092069/14 14 Formula that I would like to use: =(SUM(B1:B4="CPO")*(A1:A4="*2069/15*")*C1:C4) It doesn't work, any ideas? Thx - Dean I don't think I understand what you are trying to do with the 'SUM(B1:B4="CPO")' statement. It looks like you want to count the number of times that "CPO" shows up in the range. If this is the case, you should use 'Countif(B1:B4,"CPO")' instead. As for the second expression '(A1:A4="*2069/15*")' it looks like you want to use the same type of thing, but I am unsure about the wildcards being correct. I think you will need to use some sort of search or parse to see if the value has that particular string and then count it. A bit more complex but not impossible. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wildcard in SUM
Toyin,
Thanks for your response, I've got what I need now, but your solution has some good steps for some other applications. Thanks again. Dean " wrote: Dean, Assuming you are trying to sum the corresponding cells that end in 2069/15 in the first column and also have CPO in the second column you need to do the following: =RIGHT(TRIM(A1),7)&B1 in column (copy down) the formula to sum the relevant cells is then =SUMIF(D1:D4,"2069/15CPO",C1:C4) HTH Toyin. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wildcard in SUM
Jim,
I tried that very formula earlier after having gone to the site that you mentioned, but it never dawned on me that I didn't need to show the asterisk as a wildcard. It works absolutely great. Thanks for your response. Dean "Jim Thomlinson" wrote: Give SumProduct a try... =SUMPRODUCT(--(ISNUMBER(FIND("2069/15", $A$1:$A$4))), --($B$1:$B$4 = "CPO"),$C$1:$C$4) Take a look at this for an explanation (Example 9)... http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "Dean" wrote: I'm hoping someone will add to be able to show me what I'm doing wrong. I'll try to show a simple example. Spreadsheet view: A B C 1 11COH1092069/15 CPO 14 2 11COH1092069/15 14 3 11COH1092069/15 CPO 14 4 11COH1092069/14 14 Formula that I would like to use: =(SUM(B1:B4="CPO")*(A1:A4="*2069/15*")*C1:C4) It doesn't work, any ideas? Thx - Dean |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to CF using a wildcard | Excel Discussion (Misc queries) | |||
Using the wildcard with IF | New Users to Excel | |||
wildcard | Excel Programming | |||
sum if wildcard | New Users to Excel | |||
Wildcard | Excel Programming |