| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wild card in formula
Greetings all. I am trying to use a SUMPRODUCT formula to calculate a
number, but I need to be able to use * or LIKE, or something with the same results. I have the following, and of course it does not work =SUMPRODUCT((A6:A100 = "*COURSES")*(B6:B100)) I will have several more columns involved once I get this part to work, thus the SUMPRODUCT. Anyhow, I need it to sum B6:B100 where A6:A100 contains the word "courses". I saw another post that kind of did something similar using SEARCH(), but I could not get it to work. Thank you. Greg |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wild card in formula
This is what I had in mind, but I can't get it to work:
=SUMPRODUCT((=IF(SEARCH("COURSES",A6:A100,1) < 0,1,0))*(B6:B100)) "Greg Snidow" wrote: Greetings all. I am trying to use a SUMPRODUCT formula to calculate a number, but I need to be able to use * or LIKE, or something with the same results. I have the following, and of course it does not work =SUMPRODUCT((A6:A100 = "*COURSES")*(B6:B100)) I will have several more columns involved once I get this part to work, thus the SUMPRODUCT. Anyhow, I need it to sum B6:B100 where A6:A100 contains the word "courses". I saw another post that kind of did something similar using SEARCH(), but I could not get it to work. Thank you. Greg |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wild card in formula
Try
=SUM(IF(ISERROR(SEARCH("COURSES",A6:A100)),0,B6:B1 00)) This is an array formula which must be entered with Ctrl+Shift+Enter and NOT just by pressing Enter. If you do it correctly Excel will put curly brackets around the formula {}. You can't type these yourself. Hope this helps, Hutch "Greg Snidow" wrote: Greetings all. I am trying to use a SUMPRODUCT formula to calculate a number, but I need to be able to use * or LIKE, or something with the same results. I have the following, and of course it does not work =SUMPRODUCT((A6:A100 = "*COURSES")*(B6:B100)) I will have several more columns involved once I get this part to work, thus the SUMPRODUCT. Anyhow, I need it to sum B6:B100 where A6:A100 contains the word "courses". I saw another post that kind of did something similar using SEARCH(), but I could not get it to work. Thank you. Greg |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wild card in formula
This should work...
=SUMPRODUCT((RIGHT(A6:A100,7) = "COURSES")*(B6:B100)) -- Rick (MVP - Excel) "Greg Snidow" wrote in message ... Greetings all. I am trying to use a SUMPRODUCT formula to calculate a number, but I need to be able to use * or LIKE, or something with the same results. I have the following, and of course it does not work =SUMPRODUCT((A6:A100 = "*COURSES")*(B6:B100)) I will have several more columns involved once I get this part to work, thus the SUMPRODUCT. Anyhow, I need it to sum B6:B100 where A6:A100 contains the word "courses". I saw another post that kind of did something similar using SEARCH(), but I could not get it to work. Thank you. Greg |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wild card in formula
Tom, that worked like a charm for just the two columns. Thank you. However,
I will need to include several more columns, so how could I incorporate this using SUMPRODUCT? "Tom Hutchins" wrote: Try =SUM(IF(ISERROR(SEARCH("COURSES",A6:A100)),0,B6:B1 00)) This is an array formula which must be entered with Ctrl+Shift+Enter and NOT just by pressing Enter. If you do it correctly Excel will put curly brackets around the formula {}. You can't type these yourself. Hope this helps, Hutch "Greg Snidow" wrote: Greetings all. I am trying to use a SUMPRODUCT formula to calculate a number, but I need to be able to use * or LIKE, or something with the same results. I have the following, and of course it does not work =SUMPRODUCT((A6:A100 = "*COURSES")*(B6:B100)) I will have several more columns involved once I get this part to work, thus the SUMPRODUCT. Anyhow, I need it to sum B6:B100 where A6:A100 contains the word "courses". I saw another post that kind of did something similar using SEARCH(), but I could not get it to work. Thank you. Greg |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wild card in formula
I got it to work!
=SUMPRODUCT((IF(ISERROR(SEARCH("COURSES",A6:A100)) ,0,1)*(B6:B100))) "Tom Hutchins" wrote: Try =SUM(IF(ISERROR(SEARCH("COURSES",A6:A100)),0,B6:B1 00)) This is an array formula which must be entered with Ctrl+Shift+Enter and NOT just by pressing Enter. If you do it correctly Excel will put curly brackets around the formula {}. You can't type these yourself. Hope this helps, Hutch "Greg Snidow" wrote: Greetings all. I am trying to use a SUMPRODUCT formula to calculate a number, but I need to be able to use * or LIKE, or something with the same results. I have the following, and of course it does not work =SUMPRODUCT((A6:A100 = "*COURSES")*(B6:B100)) I will have several more columns involved once I get this part to work, thus the SUMPRODUCT. Anyhow, I need it to sum B6:B100 where A6:A100 contains the word "courses". I saw another post that kind of did something similar using SEARCH(), but I could not get it to work. Thank you. Greg |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wild card in formula
Dang it!!! I got it to work, but then I typed it again and I get the #N/A
error. Now when I type the exact same thing as I did first time, I get a message saying "blah, blah...not enough parenthesis..." and it adds an extra one at the end, and I can see it now where I copied and pasted below. Any ideas? I am completely baffled. "Greg Snidow" wrote: I got it to work! =SUMPRODUCT((IF(ISERROR(SEARCH("COURSES",A6:A100)) ,0,1)*(B6:B100))) "Tom Hutchins" wrote: Try =SUM(IF(ISERROR(SEARCH("COURSES",A6:A100)),0,B6:B1 00)) This is an array formula which must be entered with Ctrl+Shift+Enter and NOT just by pressing Enter. If you do it correctly Excel will put curly brackets around the formula {}. You can't type these yourself. Hope this helps, Hutch "Greg Snidow" wrote: Greetings all. I am trying to use a SUMPRODUCT formula to calculate a number, but I need to be able to use * or LIKE, or something with the same results. I have the following, and of course it does not work =SUMPRODUCT((A6:A100 = "*COURSES")*(B6:B100)) I will have several more columns involved once I get this part to work, thus the SUMPRODUCT. Anyhow, I need it to sum B6:B100 where A6:A100 contains the word "courses". I saw another post that kind of did something similar using SEARCH(), but I could not get it to work. Thank you. Greg |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wild card in formula
I had some text at the bottom of the new sheet in column A that was messing
it up. No problem. "Greg Snidow" wrote: Dang it!!! I got it to work, but then I typed it again and I get the #N/A error. Now when I type the exact same thing as I did first time, I get a message saying "blah, blah...not enough parenthesis..." and it adds an extra one at the end, and I can see it now where I copied and pasted below. Any ideas? I am completely baffled. "Greg Snidow" wrote: I got it to work! =SUMPRODUCT((IF(ISERROR(SEARCH("COURSES",A6:A100)) ,0,1)*(B6:B100))) "Tom Hutchins" wrote: Try =SUM(IF(ISERROR(SEARCH("COURSES",A6:A100)),0,B6:B1 00)) This is an array formula which must be entered with Ctrl+Shift+Enter and NOT just by pressing Enter. If you do it correctly Excel will put curly brackets around the formula {}. You can't type these yourself. Hope this helps, Hutch "Greg Snidow" wrote: Greetings all. I am trying to use a SUMPRODUCT formula to calculate a number, but I need to be able to use * or LIKE, or something with the same results. I have the following, and of course it does not work =SUMPRODUCT((A6:A100 = "*COURSES")*(B6:B100)) I will have several more columns involved once I get this part to work, thus the SUMPRODUCT. Anyhow, I need it to sum B6:B100 where A6:A100 contains the word "courses". I saw another post that kind of did something similar using SEARCH(), but I could not get it to work. Thank you. Greg |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| wild card in SUMPRODUCT formula | Excel Worksheet Functions | |||
| how do I use wild card in SUMPRODUCT formula? | Excel Worksheet Functions | |||
| wild card?? formula question please | Excel Discussion (Misc queries) | |||
| Using wild card in IF formula | Excel Discussion (Misc queries) | |||
| wild card -- help with formula | Excel Discussion (Misc queries) | |||
Linear Mode
