Multiple conditions within a sumif function...

[Zcwilkins]

I searched around and apparently the subject isn't possible, it was suggested to use sumproduct, which I looked at, but don't really understand...

What I'm trying to do is create a function which checks two dates, if both the dates are within the specified ranges, the function would return the sum of a specific range. I've determined that I can't specify actual date ranges, but must have the date converted to Excel's serial number date format to effectively specify the range, but I can't figure out the rest.

The sheet I'm working on is too large to attach, so below is an external link to it, beware, it is ugly. Below are the fields I'm looking at, all of which are on the Tracking sheet.

U2 through U1508 - 1st date check
W2 through W1508 - 2nd date check
X2 through X1508 - range of cells to total

Just a re-iteration that I'm checking the dates for a range, example I'm looking at for U2 through U1508 is 9/1/06 - 9/30/06 (38961 through 38990 in excel's number format) and 1/1/07 - 1/31/07 for W2 through W1508.

The final result should be returned in B7 on the Points sheet.

Any help would be appreciated!

http://www.wilkinsinc.net/randomhost...iveTracker.zip

[duane]

something like this should work - you need to adjust the ranges

=SUMPRODUCT(($A$4:$A$37>=DATEVALUE("9/1/2006"))*($A$4:$A$37<=DATEVALUE("9/30/2006"))*($B$4:$B$37>=DATEVALUE("1/1/2007"))*($B$4:$B$37<=DATEVALUE("1/31/2007"))*($C$4:$C$37))

[Maistrye]

This formula in B7 should do the trick:

Please Login or Register  to view this content.

Scott

[Zcwilkins]

This formula in B7 should do the trick:

Please Login or Register  to view this content.

Scott

Perfect, much appreciated!

I also noticed this goes straight off the date format so me converting the dates entered into Excel's number format isn't necessary. This cleans it up as well!

[Teethless mama]

I searched around and apparently the subject isn't possible, it was suggested to use sumproduct, which I looked at, but don't really understand...

What I'm trying to do is create a function which checks two dates, if both the dates are within the specified ranges, the function would return the sum of a specific range. I've determined that I can't specify actual date ranges, but must have the date converted to Excel's serial number date format to effectively specify the range, but I can't figure out the rest.

The sheet I'm working on is too large to attach, so below is an external link to it, beware, it is ugly. Below are the fields I'm looking at, all of which are on the Tracking sheet.

U2 through U1508 - 1st date check
W2 through W1508 - 2nd date check
X2 through X1508 - range of cells to total

Just a re-iteration that I'm checking the dates for a range, example I'm looking at for U2 through U1508 is 9/1/06 - 9/30/06 (38961 through 38990 in excel's number format) and 1/1/07 - 1/31/07 for W2 through W1508.

The final result should be returned in B7 on the Points sheet.

Any help would be appreciated!

http://www.wilkinsinc.net/randomhost...iveTracker.zip

=SUMPRODUCT((TEXT(A2:A20,"mmm yy")="Sep 06")*(TEXT(B2:B20,"mmm yy")="Jan 07"),C2:C20)

I use range A2:A20 for 1st date check
B2:B20 for 2nd date check
C2:C20 range of cells to total
Adjust your range to suit