conditional criteria in DSUM

[Guest]

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!

 

[Bill Kuunders]

=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

 

[Bill Kuunders]

Forgot to mention that C1 should be formatted as text.

 

[Guest]

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!

 

[Dave Peterson]

=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.)

 

[Guest]

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

 

[Guest]

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: Model>30020000000; 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]

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))