Home |
Search |
Today's Posts |
#1
|
|||
|
|||
conditional criteria in DSUM
I have a large spreadsheet of data containing part numbers. The part numbers
are entered as numbers and then custom formatted to display dashes (i.e, part number 1234567890 displays as 123-456-7890). I am trying to use a DSUM formula that will add the beginning inventory (rows) for a month (column) for a particular group of parts. For example, all parts that begin with "3002" (i.e., 30020007409). I cannot for the life of me get the criteria to recognize the part number. I have tried criteria such as "30020000000", and "3002*". Nothing is working. It's like Excel either isn't recognizing the number OR it can't do DSUM based on more than one piece of criteria (which it should be able to do). Help! |
#2
|
|||
|
|||
=SUMPRODUCT(--(LEFT(A1:A20,4)="3002"),B1:B20)
should do the trick You can get it to refer to a cell as well i.e. =SUMPRODUCT(--(LEFT(A1:A20,4)=C1),B1:B20) and enter the 4 digits in C1 Regards -- Greetings from New Zealand Bill K "S. H. Drew" wrote in message ... I have a large spreadsheet of data containing part numbers. The part numbers are entered as numbers and then custom formatted to display dashes (i.e, part number 1234567890 displays as 123-456-7890). I am trying to use a DSUM formula that will add the beginning inventory (rows) for a month (column) for a particular group of parts. For example, all parts that begin with "3002" (i.e., 30020007409). I cannot for the life of me get the criteria to recognize the part number. I have tried criteria such as "30020000000", and "3002*". Nothing is working. It's like Excel either isn't recognizing the number OR it can't do DSUM based on more than one piece of criteria (which it should be able to do). Help! |
#3
|
|||
|
|||
Forgot to mention that C1 should be formatted as text.
"Bill Kuunders" wrote in message ... =SUMPRODUCT(--(LEFT(A1:A20,4)="3002"),B1:B20) should do the trick You can get it to refer to a cell as well i.e. =SUMPRODUCT(--(LEFT(A1:A20,4)=C1),B1:B20) and enter the 4 digits in C1 Regards -- Greetings from New Zealand Bill K "S. H. Drew" wrote in message ... I have a large spreadsheet of data containing part numbers. The part numbers are entered as numbers and then custom formatted to display dashes (i.e, part number 1234567890 displays as 123-456-7890). I am trying to use a DSUM formula that will add the beginning inventory (rows) for a month (column) for a particular group of parts. For example, all parts that begin with "3002" (i.e., 30020007409). I cannot for the life of me get the criteria to recognize the part number. I have tried criteria such as "30020000000", and "3002*". Nothing is working. It's like Excel either isn't recognizing the number OR it can't do DSUM based on more than one piece of criteria (which it should be able to do). Help! |
#4
|
|||
|
|||
I need some clarification. What are A1:A20 and B1:B20 referring to? Also,
doesn't SUMPRODUCT multiply ranges? I'm only doing addition. Thanks! "Bill Kuunders" wrote: Forgot to mention that C1 should be formatted as text. "Bill Kuunders" wrote in message ... =SUMPRODUCT(--(LEFT(A1:A20,4)="3002"),B1:B20) should do the trick You can get it to refer to a cell as well i.e. =SUMPRODUCT(--(LEFT(A1:A20,4)=C1),B1:B20) and enter the 4 digits in C1 Regards -- Greetings from New Zealand Bill K "S. H. Drew" wrote in message ... I have a large spreadsheet of data containing part numbers. The part numbers are entered as numbers and then custom formatted to display dashes (i.e, part number 1234567890 displays as 123-456-7890). I am trying to use a DSUM formula that will add the beginning inventory (rows) for a month (column) for a particular group of parts. For example, all parts that begin with "3002" (i.e., 30020007409). I cannot for the life of me get the criteria to recognize the part number. I have tried criteria such as "30020000000", and "3002*". Nothing is working. It's like Excel either isn't recognizing the number OR it can't do DSUM based on more than one piece of criteria (which it should be able to do). Help! |
#5
|
|||
|
|||
=sumproduct() multiplies (and adds) numbers. Those numbers could be in ranges
(say B1:B20), though. A1:A20 would be the range where you typed the part numbers (adjust it if you need to). left(a1:a20,4)="3002" returns a series of boolean values (true/falses) Because =sumproduct() likes to work with numbers, those booleans need to be converted to numbers (0's and 1's). One way of doing that is to use --(). The first negative sign converts True to -1, the second converts that -1 to +1. (Falses get changed to 0, then to 0 (again).) b1:b20 would be the quantity associated with the part numbers in A1:A20. (those ranges need to be the same size--but not the whole column.) S. H. Drew wrote: I need some clarification. What are A1:A20 and B1:B20 referring to? Also, doesn't SUMPRODUCT multiply ranges? I'm only doing addition. Thanks! "Bill Kuunders" wrote: Forgot to mention that C1 should be formatted as text. "Bill Kuunders" wrote in message ... =SUMPRODUCT(--(LEFT(A1:A20,4)="3002"),B1:B20) should do the trick You can get it to refer to a cell as well i.e. =SUMPRODUCT(--(LEFT(A1:A20,4)=C1),B1:B20) and enter the 4 digits in C1 Regards -- Greetings from New Zealand Bill K "S. H. Drew" wrote in message ... I have a large spreadsheet of data containing part numbers. The part numbers are entered as numbers and then custom formatted to display dashes (i.e, part number 1234567890 displays as 123-456-7890). I am trying to use a DSUM formula that will add the beginning inventory (rows) for a month (column) for a particular group of parts. For example, all parts that begin with "3002" (i.e., 30020007409). I cannot for the life of me get the criteria to recognize the part number. I have tried criteria such as "30020000000", and "3002*". Nothing is working. It's like Excel either isn't recognizing the number OR it can't do DSUM based on more than one piece of criteria (which it should be able to do). Help! -- Dave Peterson |
#6
|
|||
|
|||
Thanks for the clarification. Unfortunately, the formula isn't working.
Here's an idea of how the data in this spreadsheet is arranged; without the formatting, it probably doesn't make much sense, though. What I'm looking for is a formula that will add all the figures for, say, item A for the month of January for all model numbers that begin with "3002": ITEM JAN FEB MAR 30020005732 A 1 2 7 B 5 1 3 C 1 6 4 ITEM JAN FEB MAR 30020005732 A 1 2 7 B 5 1 3 C 1 6 4 "Dave Peterson" wrote: =sumproduct() multiplies (and adds) numbers. Those numbers could be in ranges (say B1:B20), though. A1:A20 would be the range where you typed the part numbers (adjust it if you need to). left(a1:a20,4)="3002" returns a series of boolean values (true/falses) Because =sumproduct() likes to work with numbers, those booleans need to be converted to numbers (0's and 1's). One way of doing that is to use --(). The first negative sign converts True to -1, the second converts that -1 to +1. (Falses get changed to 0, then to 0 (again).) b1:b20 would be the quantity associated with the part numbers in A1:A20. (those ranges need to be the same size--but not the whole column.) S. H. Drew wrote: I need some clarification. What are A1:A20 and B1:B20 referring to? Also, doesn't SUMPRODUCT multiply ranges? I'm only doing addition. Thanks! "Bill Kuunders" wrote: Forgot to mention that C1 should be formatted as text. "Bill Kuunders" wrote in message ... =SUMPRODUCT(--(LEFT(A1:A20,4)="3002"),B1:B20) should do the trick You can get it to refer to a cell as well i.e. =SUMPRODUCT(--(LEFT(A1:A20,4)=C1),B1:B20) and enter the 4 digits in C1 Regards -- Greetings from New Zealand Bill K "S. H. Drew" wrote in message ... I have a large spreadsheet of data containing part numbers. The part numbers are entered as numbers and then custom formatted to display dashes (i.e, part number 1234567890 displays as 123-456-7890). I am trying to use a DSUM formula that will add the beginning inventory (rows) for a month (column) for a particular group of parts. For example, all parts that begin with "3002" (i.e., 30020007409). I cannot for the life of me get the criteria to recognize the part number. I have tried criteria such as "30020000000", and "3002*". Nothing is working. It's like Excel either isn't recognizing the number OR it can't do DSUM based on more than one piece of criteria (which it should be able to do). Help! -- Dave Peterson |
#7
|
|||
|
|||
Hi there! FYI, I just created a small database to run a simple DSUM test. I
tried formatting the numbers as number and I also tried formatting them as text. The criteria in neither case (for number: Model30020000000; for text: Model 3002*; I also tried Model '3002* for the text). In both cases, the result of the formula was #VALUE!. This isn't the first time I've had problems using DSUM with numbers as the criteria. I'm starting to think there's some setting somewhere I need to change. MODEL DATA 30020005732 1 30020005732 1 30020005732 1 30030005731 2 30030005731 2 30030005731 2 30030005731 2 30030005731 2 "Dave Peterson" wrote: =sumproduct() multiplies (and adds) numbers. Those numbers could be in ranges (say B1:B20), though. A1:A20 would be the range where you typed the part numbers (adjust it if you need to). left(a1:a20,4)="3002" returns a series of boolean values (true/falses) Because =sumproduct() likes to work with numbers, those booleans need to be converted to numbers (0's and 1's). One way of doing that is to use --(). The first negative sign converts True to -1, the second converts that -1 to +1. (Falses get changed to 0, then to 0 (again).) b1:b20 would be the quantity associated with the part numbers in A1:A20. (those ranges need to be the same size--but not the whole column.) S. H. Drew wrote: I need some clarification. What are A1:A20 and B1:B20 referring to? Also, doesn't SUMPRODUCT multiply ranges? I'm only doing addition. Thanks! "Bill Kuunders" wrote: Forgot to mention that C1 should be formatted as text. "Bill Kuunders" wrote in message ... =SUMPRODUCT(--(LEFT(A1:A20,4)="3002"),B1:B20) should do the trick You can get it to refer to a cell as well i.e. =SUMPRODUCT(--(LEFT(A1:A20,4)=C1),B1:B20) and enter the 4 digits in C1 Regards -- Greetings from New Zealand Bill K "S. H. Drew" wrote in message ... I have a large spreadsheet of data containing part numbers. The part numbers are entered as numbers and then custom formatted to display dashes (i.e, part number 1234567890 displays as 123-456-7890). I am trying to use a DSUM formula that will add the beginning inventory (rows) for a month (column) for a particular group of parts. For example, all parts that begin with "3002" (i.e., 30020007409). I cannot for the life of me get the criteria to recognize the part number. I have tried criteria such as "30020000000", and "3002*". Nothing is working. It's like Excel either isn't recognizing the number OR it can't do DSUM based on more than one piece of criteria (which it should be able to do). Help! -- Dave Peterson |
#8
|
|||
|
|||
First, I like to put all the information I need on each line.
I'd use one of the techniques at Debra Dalgleish's site to fill those blank cells: http://www.contextures.com/xlDataEntry02.html Then you could use: =sumproduct(--(left(e1:e20,4)="3002"),--(a1:a20="A"),b1:b20)) S. H. Drew wrote: Thanks for the clarification. Unfortunately, the formula isn't working. Here's an idea of how the data in this spreadsheet is arranged; without the formatting, it probably doesn't make much sense, though. What I'm looking for is a formula that will add all the figures for, say, item A for the month of January for all model numbers that begin with "3002": ITEM JAN FEB MAR 30020005732 A 1 2 7 B 5 1 3 C 1 6 4 ITEM JAN FEB MAR 30020005732 A 1 2 7 B 5 1 3 C 1 6 4 "Dave Peterson" wrote: =sumproduct() multiplies (and adds) numbers. Those numbers could be in ranges (say B1:B20), though. A1:A20 would be the range where you typed the part numbers (adjust it if you need to). left(a1:a20,4)="3002" returns a series of boolean values (true/falses) Because =sumproduct() likes to work with numbers, those booleans need to be converted to numbers (0's and 1's). One way of doing that is to use --(). The first negative sign converts True to -1, the second converts that -1 to +1. (Falses get changed to 0, then to 0 (again).) b1:b20 would be the quantity associated with the part numbers in A1:A20. (those ranges need to be the same size--but not the whole column.) S. H. Drew wrote: I need some clarification. What are A1:A20 and B1:B20 referring to? Also, doesn't SUMPRODUCT multiply ranges? I'm only doing addition. Thanks! "Bill Kuunders" wrote: Forgot to mention that C1 should be formatted as text. "Bill Kuunders" wrote in message ... =SUMPRODUCT(--(LEFT(A1:A20,4)="3002"),B1:B20) should do the trick You can get it to refer to a cell as well i.e. =SUMPRODUCT(--(LEFT(A1:A20,4)=C1),B1:B20) and enter the 4 digits in C1 Regards -- Greetings from New Zealand Bill K "S. H. Drew" wrote in message ... I have a large spreadsheet of data containing part numbers. The part numbers are entered as numbers and then custom formatted to display dashes (i.e, part number 1234567890 displays as 123-456-7890). I am trying to use a DSUM formula that will add the beginning inventory (rows) for a month (column) for a particular group of parts. For example, all parts that begin with "3002" (i.e., 30020007409). I cannot for the life of me get the criteria to recognize the part number. I have tried criteria such as "30020000000", and "3002*". Nothing is working. It's like Excel either isn't recognizing the number OR it can't do DSUM based on more than one piece of criteria (which it should be able to do). Help! -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dsum with date criteria | Excel Worksheet Functions | |||
how do I isolate only one row in the criteria for the dsum functi. | Excel Worksheet Functions | |||
conditional formula - based on 2 separate criteria | Excel Worksheet Functions | |||
DSUM and DCount when criteria values are similar | Excel Worksheet Functions | |||
Print cells that meet conditional formatting criteria | Excel Discussion (Misc queries) |