Nested function with date ranges

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello everyone,

Having problems with the function below:

Explained in English, this formula is to sum the results in a range provided that they meet the following criteria:
*are at 'DRW' status
*and are in the date range 1/5/2004 - 31/5/2004.

It's not quite working though-seems to pick up some dates outside of the range (but not all-strange??).

Here's the formula:

=SUM(IF($H$32:$H$526="DRW",$I$32:$I$526,0),AND($C$32:$C$526>=38108,$I$32:$I$526,0),AND($C$32:$C$526<=38138,$I$32:$I$526,0))

(if my intelligence is correct >=38108 means 1st May 2004 or later and <=38138 means 31st May or earlier)

What I'd really like is a way that I can get this formula

(i) to be accurate

(ii) to be able to update for each new month. I imagine it must be possible to have the formula point to a cell with a number range for the month based on selecting the current month from a drop-down list.


Any assistance would be much appreciated.
 
Hi
try
=SUMPRODUCT(--($H$32:$H$526="DRW),--($C$32:$C$526>=DATE
(2004,5,1)),--($C$32:$C$526<=DATE(2004,5,31)),$I$32:$I$526)


-----Original Message-----
Hello everyone,

Having problems with the function below:

Explained in English, this formula is to sum the results
Click to expand...
in a range provided that they meet the following criteria:
*are at 'DRW' status
*and are in the date range 1/5/2004 - 31/5/2004.

It's not quite working though-seems to pick up some dates
Click to expand...
outside of the range (but not all-strange??).
Here's the formula:

=SUM(IF($H$32:$H$526="DRW",$I$32:$I$526,0),AND ($C$32:$C$526>=38108,$I$32:$I$526,0),AND
($C$32:$C$526<=38138,$I$32:$I$526,0))

(if my intelligence is correct >=38108 means 1st May 2004
Click to expand...
or later and <=38138 means 31st May or earlier)
What I'd really like is a way that I can get this formula

(i) to be accurate

(ii) to be able to update for each new month. I imagine
Click to expand...
it must be possible to have the formula point to a cell
with a number range for the month based on selecting the
current month from a drop-down list.
 
Thankyou Frank. Your formula works a treat.

Did you see the second part of my request though? Ultimately what I want to do is have this formula able to run each month without needing to retype the formula with that month's date range. Is it possible to have such a formula point to a cell/s with the date ranges I require?

Ie so next month I can select June from a drop-down list I have on the sheet, and this will flow thru and auto-update the formula to only calculate results for that month

I know this is tricky-any assistance would be very much appreciated

Kind regards,
 
Hi
if you put the year in cell A1 and the month number (1-12) in cell B1
use:
=SUMPRODUCT(--($H$32:$H$526="DRW),--($C$32:$C$526>=DATE
(A1,B1,1)),--($C$32:$C$526<DATE(A1,B1+1,1)),$I$32:$I$526)
 
Frank, you are the man! Thanks for all your help, my spreadsheet is now working very nicely
 
Back
Top