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!

=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" <[email protected]> wrote in message
news:[email protected]...
>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!

Forgot to mention that C1 should be formatted as text.


"Bill Kuunders" <[email protected]> wrote in message
news:[email protected]...
> =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" <[email protected]> wrote in message
> news:[email protected]...
>>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!
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > =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" <[email protected]> wrote in message
> > news:[email protected]...
> >>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!
> >
> >
>
>
>

=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" <[email protected]> wrote in message
> > news:[email protected]...
> > > =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" <[email protected]> wrote in message
> > > news:[email protected]...
> > >>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

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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > =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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > >>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
>

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" 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > =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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > >>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
>

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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > =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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > >>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