Re: value of #'s in column if between dates

No need to array enter it

--

Regards,

Peo Sjoblom

"Jeremy Ellison" <JeremyEllison@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B8FDFD20-964F-483A-84A4-22B15149F198@xxxxxxxxxxxxxxxx
> I really like your formula. I was trying to get rid of the "12-12-2005"
data
> format and didn't know how. I used yours' -- I used your formula
without
> control shift enter and got the right amount. was this a fluke and
should I
> continue to enter it as an array formula or is it not necessary?
>
> Thanks....
>
> "Peo Sjoblom" wrote:
>
> > This part
> >
> > CaseData!AU2:AU1000
> >
> > must contain text, if you have values there derived by formulas then
> > something like =if(a1>2,2,"") where the result woul be "" would cause
that
> > error, I also note that the formula you pasted has 200 as year in the
second
> > condition DATE(200,12,31)
> > anyway to disregard text in AU you can change the formula to
> >
> >
=SUMPRODUCT(--(CaseData!C2:C1000>=DATE(2005,10,1)),--(CaseData!C2:C1000<=DAT
> > E(2005,12,31)),--(CaseData!AT2:AT1000="Heroin"),CaseData!AU2:AU1000)
> >
> > if you get zero as result then the numbers in AU are text, copy an empty
> > cell, select the range and do edit>paste special and select add
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> >
> > "Jeremy Ellison" <JeremyEllison@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
> > news:1CD6A520-BA59-4A6B-98E6-EAB3BD819B5B@xxxxxxxxxxxxxxxx
> > > Here's what I have been trying:
> > >
> > >
> >
=SUMPRODUCT((CaseData!C2:C1000>=DATE(2005,10,1))*(CaseData!C2:C1000<=DATE(20
> > 0,12,31))*(CaseData!AT2:AT1000="Heroin")*CaseData!AU2:AU1000)
> > >
> > >
> > >
> > > for the follwoing data
> > >
> > >
> > > C AT AU
> > > 1 Date DRUG Amount
> > > 2 10/1/5 Heroin 2.2
> > > 3 1/2/6 Heroin 5.5
> > >
> > > I get a "VALUE" error when I run this...I want it to return 2.2
because
> > row
> > > 2 is between the dates specified.
> > >
> > >
> > > Any one know how to get it to work correctly? I/m lost!
> > >
> > >
> > > IF I do come up with the answer for this... I will want it to then
look at
> > 2
> > > more columkns with the same data... Example:
> > >
> > > C AT AU
> > AV
> > > AW
> > > 1 Date DRUG Amount Drug2
> > > Amount2
> > > 2 1/1/6 Heroin 2.2 Grams Meth
> > > 2.5
> > > 3 1/2/7 Heroin 5.5 Grams MJ
> > > 1.1
> > >
> > > The formula will need to add AU & AW together, if C is between dates
and
> > > AT=a certain drug....
> > >
> > > THanks!
> >
> >
> >


.

Relevant Pages