Here's what I have been trying:

=SUMPRODUCT((CaseData!C2:C1000=DATE(2005,10,1))*( CaseData!C2:C1000<=DATE(200,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!

I used the circle invalid dat button and found some data in one of my columns
hat was incorrectly put in there. I then removed it and this gave me the
total of what i was looking for.

I will now try to combine my total of total 1 and total 2. feel free to
post if you have suggestions while i work on this. thank you.

"Jeremy Ellison" wrote:

Here's what I have been trying:

=SUMPRODUCT((CaseData!C2:C1000=DATE(2005,10,1))*( CaseData!C2:C1000<=DATE(200,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!

This part

CaseData!AU2:AU1000

must contain text, if you have values there derived by formulas then
something like =if(a12,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 editpaste special and select add
--

Regards,

Peo Sjoblom



"Jeremy Ellison" wrote in message
...
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!

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(a12,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 editpaste special and select add
--

Regards,

Peo Sjoblom



"Jeremy Ellison" wrote in message
...
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!

NOW - Is there a way to search the text for certain characters...put a wild
card in there?

the drug column has Cocaine and "Crack" Cocaine
in the column. I can't put in "crack" cocaine as the variable to search
for because I have the quotation marks around crack.

How can i get it to find this string without changing the column "crack"
cocaine to something else.

"Peo Sjoblom" wrote:

This part

CaseData!AU2:AU1000

must contain text, if you have values there derived by formulas then
something like =if(a12,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 editpaste special and select add
--

Regards,

Peo Sjoblom



"Jeremy Ellison" wrote in message
...
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!

No need to array enter it

--

Regards,

Peo Sjoblom

"Jeremy Ellison" wrote in message
...
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(a12,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 editpaste special and select add
--

Regards,

Peo Sjoblom



"Jeremy Ellison" wrote in
message
...
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!

You can use this

--(ISNUMBER(SEARCH("cocaine",CaseData!AT2:AT1000)))


--

Regards,

Peo Sjoblom





"Jeremy Ellison" wrote in message
...
NOW - Is there a way to search the text for certain characters...put a
wild
card in there?

the drug column has Cocaine and "Crack" Cocaine
in the column. I can't put in "crack" cocaine as the variable to search
for because I have the quotation marks around crack.

How can i get it to find this string without changing the column "crack"
cocaine to something else.

"Peo Sjoblom" wrote:

This part

CaseData!AU2:AU1000

must contain text, if you have values there derived by formulas then
something like =if(a12,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 editpaste special and select add
--

Regards,

Peo Sjoblom



"Jeremy Ellison" wrote in
message
...
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!

AWESOME. Thank you... . playing around with this stuff really helps me
learn. I really appreciate all your help (and everyone else who has been
sending me things on here also!)

Thanks again!

"Peo Sjoblom" wrote:

You can use this

--(ISNUMBER(SEARCH("cocaine",CaseData!AT2:AT1000)))


--

Regards,

Peo Sjoblom





"Jeremy Ellison" wrote in message
...
NOW - Is there a way to search the text for certain characters...put a
wild
card in there?

the drug column has Cocaine and "Crack" Cocaine
in the column. I can't put in "crack" cocaine as the variable to search
for because I have the quotation marks around crack.

How can i get it to find this string without changing the column "crack"
cocaine to something else.

"Peo Sjoblom" wrote:

This part

CaseData!AU2:AU1000

must contain text, if you have values there derived by formulas then
something like =if(a12,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 editpaste special and select add
--

Regards,

Peo Sjoblom



"Jeremy Ellison" wrote in
message
...
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!

Thanks for the feedback


--

Regards,

Peo Sjoblom


"Jeremy Ellison" wrote in message
...
AWESOME. Thank you... . playing around with this stuff really helps me
learn. I really appreciate all your help (and everyone else who has been
sending me things on here also!)

Thanks again!

"Peo Sjoblom" wrote:

You can use this

--(ISNUMBER(SEARCH("cocaine",CaseData!AT2:AT1000)))


--

Regards,

Peo Sjoblom





"Jeremy Ellison" wrote in
message
...
NOW - Is there a way to search the text for certain characters...put
a
wild
card in there?

the drug column has Cocaine and "Crack" Cocaine
in the column. I can't put in "crack" cocaine as the variable to
search
for because I have the quotation marks around crack.

How can i get it to find this string without changing the column
"crack"
cocaine to something else.

"Peo Sjoblom" wrote:

This part

CaseData!AU2:AU1000

must contain text, if you have values there derived by formulas then
something like =if(a12,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 editpaste special and select add
--

Regards,

Peo Sjoblom



"Jeremy Ellison" wrote in
message
...
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!