formula date range
 

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)

formula date range
 

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

"Kalida Williams" skrev i
meddelelsen ...
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)

formula date range
 

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

"Kalida Williams" wrote:

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)

formula date range
 

Kalida Williams wrote:
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(20 09,9,1)*
(data!$A$2:$A$503<=DATE(2009,9,30),data!$C$2:$C$50 3)

formula date range
 

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)

--
Biff
Microsoft Excel MVP


"Kalida Williams" wrote in
message ...
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)