formula date range

[Kalida Williams]

I have dates in column A, codes in column B and amounts in column C on
spreadsheet 2 (data).

I want on sheet 1 to say that if the date falls in a certain range (ex. July
1-July 31,2009) and the code is a specific code (ex. AT) then sum the amounts
in column C.

This is the formula I was using but it's not working. I can't figure out
the date range piece of the formula.

=SUMPRODUCT(--(data!$B$2:$B$503="HP"),--(data!$A$2:$A$503="09/01/2009-09/30/2009"),data!$C$2:$C$503)

 

[Per Jessen]

Hi

Look at this:

=SUMPRODUCT(--(data!$B$2:$B$503="HP"),--(data!$A$2:$A$503>=DateValue("09/01/2009")),--(data!$A$2:$A$503<=DateValue("09/30/2009")),data!$C$2:$C$503)

Regards,
Per

 

[NBVC]

Try amending your formula to:

=SUMPRODUCT(--(data!$B$2:$B$503="HP"),--(data!$A$2:$A$503>="09/01/2009"+0),--(data!$A$2:$A$503<="09/30/2009"+0),data!$C$2:$C$503

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[Eduardo]

Hi,

=SUMPRODUCT(--(data!$B$2:$B$503="HP"),--(data!$A$2:$A$503>="09/01/2009"),--(data!$A$2:$A$503<="09/30/2009"),data!$C$2:$C$503)

if this helps please click yes thanks

 

[Glenn]

I have dates in column A, codes in column B and amounts in column C on
spreadsheet 2 (data).

I want on sheet 1 to say that if the date falls in a certain range (ex. July
1-July 31,2009) and the code is a specific code (ex. AT) then sum the amounts
in column C.

This is the formula I was using but it's not working. I can't figure out
the date range piece of the formula.

=SUMPRODUCT(--(data!$B$2:$B$503="HP"),--(data!$A$2:$A$503="09/01/2009-09/30/2009"),data!$C$2:$C$503)

Try this for your date:


=SUMPRODUCT(--(data!$B$2:$B$503="HP"),(data!$A$2:$A$503>=DATE(2009,9,1)*
(data!$A$2:$A$503<=DATE(2009,9,30),data!$C$2:$C$503)

 

[T. Valko]

If you're using Excel 2007:

Use cells to hold the criteria:

A1 = start date = 9/1/2009
B1 = end date = 9/30/2009
C1 = code = HP

=SUMIFS(Data!C2:C503,Data!A2:A503,">="&A1,Data!A2:A503,"<="&B1,Data!B2:B503,C1)