|
value of #'s in column if between dates
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! |
value of #'s in column if between dates
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! |
value of #'s in column if between dates
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! |
value of #'s in column if between dates
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! |
value of #'s in column if between dates
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! |
value of #'s in column if between dates
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! |
value of #'s in column if between dates
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! |
value of #'s in column if between dates
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! |
value of #'s in column if between dates
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! |
| All times are GMT +1. The time now is 03:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com