Date Function Question

  • Thread starter Thread starter Darin Spence
  • Start date Start date
D

Darin Spence

I have a data set that looks like this:

CodeDate: 12/28/03 (dateFormat)

DATE QTY
1/30/04 1
1/15/04 3
11/10/03 5
10/16/03 7

I'd like to do a "SUMIF" fuction as follows:

NOTE: SUMIF(range,criteria,sum_range)

SUMIF(DATE:RANGE,DATE<CodeDate,QTY:RANGE)

I can't seem to get this to work. Anyone have any ideas
on how I might be able to do this?

Thanks!
-Darin
 
Darin,
Assuming code date is entered in C1, try this:

=SUMIF(A1:A4,"<"&C1,B1:B4)


Good Luck,
Mark Graesser
(e-mail address removed)


----- Darin Spence wrote: -----

I have a data set that looks like this:

CodeDate: 12/28/03 (dateFormat)

DATE QTY
1/30/04 1
1/15/04 3
11/10/03 5
10/16/03 7

I'd like to do a "SUMIF" fuction as follows:

NOTE: SUMIF(range,criteria,sum_range)

SUMIF(DATE:RANGE,DATE<CodeDate,QTY:RANGE)

I can't seem to get this to work. Anyone have any ideas
on how I might be able to do this?

Thanks!
-Darin
 
Darin Spence said:
I have a data set that looks like this:

CodeDate: 12/28/03 (dateFormat)

DATE QTY
1/30/04 1
1/15/04 3
11/10/03 5
10/16/03 7

I'd like to do a "SUMIF" fuction as follows:

NOTE: SUMIF(range,criteria,sum_range)

SUMIF(DATE:RANGE,DATE<CodeDate,QTY:RANGE)

I can't seem to get this to work. Anyone have any ideas
on how I might be able to do this?

Thanks!
-Darin
Click to expand...

Suppose your dates are in A1:A4 and your quantities in B1:B4.
You can put the code date in the formula:
=SUMIF(A1:A4,"<"&DATE(2003,12,28),B1:B4)

Alternatively, suppose the code date is in C1. Then you can use:
=SUMIF(A1:A4,"<"&C1,B1:B4)
 
That worked like a champ!

If someone has a moment, could you step through why that
worked? The literal "<" and concatenation of C1 are what
have me a bit puzzled. What is going on there?

THANKS A BUNCH!
-Darin
 
If C1 contains, say 4, then

"<" & C1

resolves to the string

"<4"

which is a valid criterion within COUNTIF and SUMIF. Therefore

=SUMIF(A1:A4,"<"&C1,B1:B4)

therefore resolves to

=SUMIF(A1:A4,"<4",B1:B4)
 
Back
Top