| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing data between two date criteria
=SUMPRODUCT(--(I6:I250<DATE(2009,1,1)),--(ISNUMBER(I6:I250)),J6:J250)
I am using this formula to calcuate the number of square footage with an expiration date less than 1/1/09 (some of the cells contain the value "month-to-month"). I now need to write this same formula for expiration dates between 1/1/09 and 6/30/09. How do I get multiple date parameters - I keep getting errors trying to use the AND function. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summing data between two date criteria
=SUMPRODUCT(--(I6:I250=DATE(2009,1,1)),--(I6:I250<=DATE(2009,6,30)),J6:J250)
"Anna Wood" wrote: =SUMPRODUCT(--(I6:I250<DATE(2009,1,1)),--(ISNUMBER(I6:I250)),J6:J250) I am using this formula to calcuate the number of square footage with an expiration date less than 1/1/09 (some of the cells contain the value "month-to-month"). I now need to write this same formula for expiration dates between 1/1/09 and 6/30/09. How do I get multiple date parameters - I keep getting errors trying to use the AND function. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summing data between two date criteria
Use cells to hold your date boundaries:
A1 = lower date boundary B1 = upper date boundary Then: =SUMPRODUCT(--(I6:I250=A1),--(I6:I250<=B1),--(ISNUMBER(I6:I250)),J6:J250) You may not need the --(ISNUMBER(I6:I250)) test. Try removing and see if it works. -- Biff Microsoft Excel MVP "Anna Wood" wrote: =SUMPRODUCT(--(I6:I250<DATE(2009,1,1)),--(ISNUMBER(I6:I250)),J6:J250) I am using this formula to calcuate the number of square footage with an expiration date less than 1/1/09 (some of the cells contain the value "month-to-month"). I now need to write this same formula for expiration dates between 1/1/09 and 6/30/09. How do I get multiple date parameters - I keep getting errors trying to use the AND function. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summing data between two date criteria
If I remove the ISNUMBER, the formula results in 278,025. With the ISNUMBER,
the formula results in 10,000. 10,000 is the correct number. Thank you for your help in adding the second date to the formula. "T. Valko" wrote: Use cells to hold your date boundaries: A1 = lower date boundary B1 = upper date boundary Then: =SUMPRODUCT(--(I6:I250=A1),--(I6:I250<=B1),--(ISNUMBER(I6:I250)),J6:J250) You may not need the --(ISNUMBER(I6:I250)) test. Try removing and see if it works. -- Biff Microsoft Excel MVP "Anna Wood" wrote: =SUMPRODUCT(--(I6:I250<DATE(2009,1,1)),--(ISNUMBER(I6:I250)),J6:J250) I am using this formula to calcuate the number of square footage with an expiration date less than 1/1/09 (some of the cells contain the value "month-to-month"). I now need to write this same formula for expiration dates between 1/1/09 and 6/30/09. How do I get multiple date parameters - I keep getting errors trying to use the AND function. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summing data between two date criteria
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Anna Wood" wrote: If I remove the ISNUMBER, the formula results in 278,025. With the ISNUMBER, the formula results in 10,000. 10,000 is the correct number. Thank you for your help in adding the second date to the formula. "T. Valko" wrote: Use cells to hold your date boundaries: A1 = lower date boundary B1 = upper date boundary Then: =SUMPRODUCT(--(I6:I250=A1),--(I6:I250<=B1),--(ISNUMBER(I6:I250)),J6:J250) You may not need the --(ISNUMBER(I6:I250)) test. Try removing and see if it works. -- Biff Microsoft Excel MVP "Anna Wood" wrote: =SUMPRODUCT(--(I6:I250<DATE(2009,1,1)),--(ISNUMBER(I6:I250)),J6:J250) I am using this formula to calcuate the number of square footage with an expiration date less than 1/1/09 (some of the cells contain the value "month-to-month"). I now need to write this same formula for expiration dates between 1/1/09 and 6/30/09. How do I get multiple date parameters - I keep getting errors trying to use the AND function. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summing data between two date criteria
Hi,
ISNUMBER wasn't needed in your original formula, nor in the suggests you recieved. Your original could be =SUMPRODUCT(--(I6:I250<DATE(2009,6,1)),J6:J250) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Anna Wood" wrote: =SUMPRODUCT(--(I6:I250<DATE(2009,1,1)),--(ISNUMBER(I6:I250)),J6:J250) I am using this formula to calcuate the number of square footage with an expiration date less than 1/1/09 (some of the cells contain the value "month-to-month"). I now need to write this same formula for expiration dates between 1/1/09 and 6/30/09. How do I get multiple date parameters - I keep getting errors trying to use the AND function. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 2 Criteria Summing | Excel Worksheet Functions | |||
| Summing/Counting Data in One Column Based on Criteria Another Col | Excel Worksheet Functions | |||
| Summing Data by date from different tables | Excel Worksheet Functions | |||
| summing values in a data table based on criteria in another column | Excel Worksheet Functions | |||
| Summing data based on criteria | Excel Discussion (Misc queries) | |||
Hybrid Mode
