| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with =SUMPRODUCT
Hello,
I am trying to use the =SUMPRODUCT formula but it does not seem to be working. Not sure what I am doing wrong. Here is an example of the data: Adams, Wendy OD-Apple 6 Adams, Wendy OD-Orange 2.5 Adams, Wendy JD-Lemon 3 Adams, Wendy JD-Apple 8 Arment, Shannon JD-Cherry 25 Arment, Shannon OD-Orange 4 Arment, Shannon OD-Apple 2 Arment, Shannon JD-Blueberry 4 Baca, Ralph JD-Kiwi 4 Baca, Ralph JD-Lemon 1.5 Baca, Ralph JD-Cherry 8.5 Baca, Ralph JD-Grape 4 Baca, Ralph OD-Banana 1 Baca, Ralph OD-Apple 5 Borlan, Priscilla JD-Lemon 4 Borlan, Priscilla JD-Grape 8 Borlan, Priscilla OD-Orange 4 Borlan, Priscilla OD-Kiwi 4 Here is the formula I am trying to use: =SUMPRODUCT((Practice!A1:A18=A1)*(Practice!B1:B18= "JD")*(Practice!C1:C18)) If whats in column A matches and column B has the letters "JD" in it then sum up whats in column C. I think my problem is that I am only searching for a portion of the text in column B. Can anyone help with this? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with =SUMPRODUCT
You're looking for it to be equal to "JD", not a string containing "JD".
-- David Biddulph "parkermazk" wrote in message ... Hello, I am trying to use the =SUMPRODUCT formula but it does not seem to be working. Not sure what I am doing wrong. Here is an example of the data: Adams, Wendy OD-Apple 6 Adams, Wendy OD-Orange 2.5 Adams, Wendy JD-Lemon 3 Adams, Wendy JD-Apple 8 Arment, Shannon JD-Cherry 25 Arment, Shannon OD-Orange 4 Arment, Shannon OD-Apple 2 Arment, Shannon JD-Blueberry 4 Baca, Ralph JD-Kiwi 4 Baca, Ralph JD-Lemon 1.5 Baca, Ralph JD-Cherry 8.5 Baca, Ralph JD-Grape 4 Baca, Ralph OD-Banana 1 Baca, Ralph OD-Apple 5 Borlan, Priscilla JD-Lemon 4 Borlan, Priscilla JD-Grape 8 Borlan, Priscilla OD-Orange 4 Borlan, Priscilla OD-Kiwi 4 Here is the formula I am trying to use: =SUMPRODUCT((Practice!A1:A18=A1)*(Practice!B1:B18= "JD")*(Practice!C1:C18)) If whats in column A matches and column B has the letters "JD" in it then sum up whats in column C. I think my problem is that I am only searching for a portion of the text in column B. Can anyone help with this? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with =SUMPRODUCT
Hi,
Try this =SUMPRODUCT((Practice!A1:A18=A1)*(--ISNUMBER(SEARCH("JD",Practice!B1:B18))=1)*(Practic e!C1:C18)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "parkermazk" wrote: Hello, I am trying to use the =SUMPRODUCT formula but it does not seem to be working. Not sure what I am doing wrong. Here is an example of the data: Adams, Wendy OD-Apple 6 Adams, Wendy OD-Orange 2.5 Adams, Wendy JD-Lemon 3 Adams, Wendy JD-Apple 8 Arment, Shannon JD-Cherry 25 Arment, Shannon OD-Orange 4 Arment, Shannon OD-Apple 2 Arment, Shannon JD-Blueberry 4 Baca, Ralph JD-Kiwi 4 Baca, Ralph JD-Lemon 1.5 Baca, Ralph JD-Cherry 8.5 Baca, Ralph JD-Grape 4 Baca, Ralph OD-Banana 1 Baca, Ralph OD-Apple 5 Borlan, Priscilla JD-Lemon 4 Borlan, Priscilla JD-Grape 8 Borlan, Priscilla OD-Orange 4 Borlan, Priscilla OD-Kiwi 4 Here is the formula I am trying to use: =SUMPRODUCT((Practice!A1:A18=A1)*(Practice!B1:B18= "JD")*(Practice!C1:C18)) If whats in column A matches and column B has the letters "JD" in it then sum up whats in column C. I think my problem is that I am only searching for a portion of the text in column B. Can anyone help with this? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with =SUMPRODUCT
Try...
=SUMPRODUCT((Practice!A1:A18=A1)*(LEFT(Practice!B1 :B18,2)="JD")*(Practice!C1:C18)) or =SUMPRODUCT(--(Practice!A1:A18=A1),--(LEFT(Practice!B1:B18,2)="JD"),Practice!C1:C18) -- Domenic Microsoft MVP - Excel www.xl-central.com, "Your Quick Reference to Excel Solutions" "parkermazk" wrote in message ... Hello, I am trying to use the =SUMPRODUCT formula but it does not seem to be working. Not sure what I am doing wrong. Here is an example of the data: Adams, Wendy OD-Apple 6 Adams, Wendy OD-Orange 2.5 Adams, Wendy JD-Lemon 3 Adams, Wendy JD-Apple 8 Arment, Shannon JD-Cherry 25 Arment, Shannon OD-Orange 4 Arment, Shannon OD-Apple 2 Arment, Shannon JD-Blueberry 4 Baca, Ralph JD-Kiwi 4 Baca, Ralph JD-Lemon 1.5 Baca, Ralph JD-Cherry 8.5 Baca, Ralph JD-Grape 4 Baca, Ralph OD-Banana 1 Baca, Ralph OD-Apple 5 Borlan, Priscilla JD-Lemon 4 Borlan, Priscilla JD-Grape 8 Borlan, Priscilla OD-Orange 4 Borlan, Priscilla OD-Kiwi 4 Here is the formula I am trying to use: =SUMPRODUCT((Practice!A1:A18=A1)*(Practice!B1:B18= "JD")*(Practice!C1:C18)) If whats in column A matches and column B has the letters "JD" in it then sum up whats in column C. I think my problem is that I am only searching for a portion of the text in column B. Can anyone help with this? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with =SUMPRODUCT
Hi,
I should have added that this bit of the formula ensures it will only count JD at the start of the string (--ISNUMBER(SEARCH("JD",Practice!B1:B18))=1) If you want to count JD anywhere in the string you can use (ISNUMBER(SEARCH("JD",Practice!B1:B18))) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, Try this =SUMPRODUCT((Practice!A1:A18=A1)*(--ISNUMBER(SEARCH("JD",Practice!B1:B18))=1)*(Practic e!C1:C18)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "parkermazk" wrote: Hello, I am trying to use the =SUMPRODUCT formula but it does not seem to be working. Not sure what I am doing wrong. Here is an example of the data: Adams, Wendy OD-Apple 6 Adams, Wendy OD-Orange 2.5 Adams, Wendy JD-Lemon 3 Adams, Wendy JD-Apple 8 Arment, Shannon JD-Cherry 25 Arment, Shannon OD-Orange 4 Arment, Shannon OD-Apple 2 Arment, Shannon JD-Blueberry 4 Baca, Ralph JD-Kiwi 4 Baca, Ralph JD-Lemon 1.5 Baca, Ralph JD-Cherry 8.5 Baca, Ralph JD-Grape 4 Baca, Ralph OD-Banana 1 Baca, Ralph OD-Apple 5 Borlan, Priscilla JD-Lemon 4 Borlan, Priscilla JD-Grape 8 Borlan, Priscilla OD-Orange 4 Borlan, Priscilla OD-Kiwi 4 Here is the formula I am trying to use: =SUMPRODUCT((Practice!A1:A18=A1)*(Practice!B1:B18= "JD")*(Practice!C1:C18)) If whats in column A matches and column B has the letters "JD" in it then sum up whats in column C. I think my problem is that I am only searching for a portion of the text in column B. Can anyone help with this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| sumproduct | Excel Discussion (Misc queries) | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| sumproduct | Excel Discussion (Misc queries) | |||
Linear Mode
