Summing numbers between a date range

[Attila Fust]

I need to sum values in a row of data where the date in
the column of the row falls within a certain date range.

Here is an example of the data. The data to be summed is
in sheet one and the dates used for the criteria are in
sheet 2.

Sheet_1:

col a col b col c col d
Nov 15/92 Feb 15/93 Sep 15/93 Feb 15/94
15.00 20.00 30.00 10.00

Sheet_2:

col a col b
Jan 1/93 Dec 31/93
Jan 2/94 Dec 31/94

I basically want to sum the data in Sheet_1 that is
between a date range based on Sheet_2. For example, sum
values in sheet_2:row2 if the date in sheet_1:row1 is
between the dates in sheet_2:A1 and sheet_2:A2 (ie between
Jan 1/93 and Dec 1/93). In this example the product would
be $50.00.

I can't seem to use sumif to do this. I tried an array
formula - =SUM(IF((sheet_1!$A$1>=sheet_2!$a$1)*(sheet_1!
$b$1>=sheet_2!$b$1),sheet_1!a2:d2)) - but this did not
seem to work either. With this formla it will sum
everything.

Any suggestions?

Thanks,

Attila

 

[Domenic]

Hi Attila,

Try,

=SUMPRODUCT(--(Sheet1!A11>=Sheet2!A1),--(Sheet1!A11<=Sheet2!A2),Sheet
1!A22)

Hope this helps!

 

[Attila Fust]

Thank you, that worked well. I tried using this with a
named range that lies within a pivot table. If I extend
the range beyond the pivot table (eg. pivot ends at j10
but range extends to N10) the function returns #VALUE.

I did this in case the pivot goes beyond J10 in the
example above. Is there a way to work around this?

Attila Fust

 

[Frank Kabel]

Hi
could you post your exact formula?

 

[Soo Cheon Jheong]

Attila,

=SUMIF(Sheet1!$A$1:$D$1,">="&Sheet2!A1,Sheet1!$A$2:$D$2)
-SUMIF(Sheet1!$A$1:$D$1,">"&Sheet2!B1,Sheet1!$A$2:$D$2)


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--

 

[Guest]

Frank:

My formula is: =SUMPRODUCT(--(dates>=ctrl_1!C10),--
(dates<=ctrl_1!D10),--(EHB))

EHB is a named range which is defined as: =OFFSET(Input!
$B$9,0,0,1,COUNTA(Input!$B$9:Input!$J$9)) - it references
a row in a pivot table I have setup in the workbook (I use
the Offset function because eventually I would like the
range to be dynamic. If the pivot table is refreshed and
there are fewer columns (eg. B10:H10) then the formula
will return #VALUE!. My thought is that SUMPRODUCT has a
problem with named ranges if the number of columns change.

Any ideas?

Attila Fust

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(dates>=ctrl_1!C10),--(dates<=ctrl_1!D10),EHB)

also make sure all named ranges have trhe same dimension