Does your Column AU actually contain the word "grams"?
I thought you were just being descriptive.
If that's the case, you've got to eliminate that text.
Are you importing this data, or are you keying it in?
If you're entering it yourself, you can format the column to display
"grams", and still retain numerical integrity.
Custom format the column as:
#,###.##" grams"
If you're importing the data, you can try to eliminate the text en masse
using TTC.
Select the column and then:
<Data <Text To Columns
Check "delimited", then <Next.
Click on "space", then look in the "preview box" and make sure all the text
comes together in a single, separate column.
If you don't see a separation, you might have "non-breaking" spaces.
Click on "other", and then hold down <Alt,
And type
0160
Using the num keypad, *not* the numbers under the function keys.
If you now see separate columns,
Then <Next.
Click in that text column to select it, then click on "Do Not Import".
The header of the text column should change to "Skip Column".
Then hit <Finish.
Since you're using the asterisk form of Sumproduct, it really shouldn't
matter what format the remaining numbers actually are.
As long as they look like numbers, they should calculate.
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
"Jeremy Ellison" wrote in message
...
=SUMPRODUCT((CaseData!C2:C1000=DATE(2005,10,1))*( CaseData!C2:C1000<=DATE(20
0,12,31))*(CaseData!AT2:AT1000="Heroin")*CaseData! AU2:AU1000)
is what I put in there.... I got back a "VALUE" error....
"RagDyeR" wrote:
Just add another argument to the original formula:
Watch out though ... you're switching amount columns in your questions!
=SUMPRODUCT((C2:C1000=DATE(2006,1,1))*(C2:C1000<= DATE(2006,3,31))*(AT2:AT10
00="Heroin")*AL2:AL1000)
I would suggest that you consider using specific cells to designate your
variables, so that you won't have to revise the formula itself, if and
when
you change time periods and drugs.
A1 = start date
A2 = end date
A3 = drug
=SUMPRODUCT((C2:C1000=A1)*(C2:C1000<=A2)*(AT2:AT1 000=A3)*AL2:AL1000)
--
HTH,
RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===
"Jeremy Ellison" wrote in
message
...
Very helpful, from both of you.... I think that would work. I forgot to
mention that I also need to select which numbers to add within the
column....
C AT AU
1 Date DRUG Amount
2 1/1/6 Heroin 2.2 Grams
3 1/2/7 Heroin 5.5 Grams
I need it to add just the "heroin" -- or what ever other drug I want to
add
up, recovered between 1/1/6 adn 3/31/6.... teh result of this formula
from
above should then be 2.2 grams...
Thank you again for your help...I am learning lots from reading how u
put
this stuff into action!
"Rowan Drummond" wrote:
You could try:
=SUMIF($C$2:$C$21,"<=03/31/06",$AL$2:$AL$21)-SUMIF($C$2:$C$21,"<01/01/06",$A
L$2:$AL$21)
Format as general.
Hope this helps
Rowan
Jeremy Ellison wrote:
I want to add a column of numbers (column AL, which contains #.#
Grams)
if
column C (dates) is between a range of dates (ie. between 1/1/06
and
3/31/06).
I could use sumproduct if it was text, to count the number of
occurrances of
a certain text, but how to I get it to add numbers....?