Re: conditional criteria in DSUM
- From: "S. H. Drew" <SHDrew@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 2 May 2005 10:01:06 -0700
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" <bill.kuunders@xxxxxxxxxx> wrote in message
> > > news:eETb8ZRTFHA.544@xxxxxxxxxxxxxxxxxxxxxxx
> > > > =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" <SHDrew@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > news:87FC21B4-21BA-413B-8958-EEEAD8BA7C84@xxxxxxxxxxxxxxxx
> > > >>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
>
.
- Follow-Ups:
- Re: conditional criteria in DSUM
- From: Dave Peterson
- Re: conditional criteria in DSUM
- References:
- Re: conditional criteria in DSUM
- From: S. H. Drew
- Re: conditional criteria in DSUM
- From: Dave Peterson
- Re: conditional criteria in DSUM
- Prev by Date: Re: can not add data to existing Excel worksheet
- Next by Date: RE: Excel: Clear all cell entries EXCEPT formulas?
- Previous by thread: Re: conditional criteria in DSUM
- Next by thread: Re: conditional criteria in DSUM
- Index(es):
Relevant Pages
|
