Add numbers in column b if date is between certain criteria

J

Jeremy Ellison

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....?
 
R

Ragdyer

Try this:

=SUMPRODUCT((C2:C1000>=DATE(2006,1,1))*(C2:C1000<=DATE(2006,3,31))*AL2:AL1000)
 
R

Rowan Drummond

You could try:
=SUMIF($C$2:$C$21,"<=03/31/06",$AL$2:$AL$21)-SUMIF($C$2:$C$21,"<01/01/06",$AL$2:$AL$21)
Format as general.

Hope this helps
Rowan
 
J

Jeremy Ellison

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!
 
R

RagDyeR

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:AT1000=A3)*AL2:AL1000)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

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!
 
J

Jeremy Ellison

=SUMPRODUCT((CaseData!C2:C1000>=DATE(2005,10,1))*(CaseData!C2:C1000<=DATE(200,12,31))*(CaseData!AT2:AT1000="Heroin")*CaseData!AU2:AU1000)

is what I put in there.... I got back a "VALUE" error....
 
D

Dave Peterson

Do you have any text in: CaseData!AU2:AU1000

Do you have any error values in any of the ranges?
 
R

RagDyer

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!
==============================================
 
Top