Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use SUMPRODUCT with a Date
Hey all,
I have a SUMPRODUCT equation (below) in which I want to look for a specific name and then check the date column to see if the date is in Q1, Q2, Q3, etc... This is what I have: =SUMPRODUCT(--('Laptops - All'!$X$2:$X$2000"01/01/2006"),--('Laptops - All'!$AD$2:$AD$2000="BRENNAN JR")) Not working for me, returns all 0's - what can I do to specify 1. Less than 01/01/2006 2. Between 01/01/2006 - 03/31/2006 3. Between 04/01/2006 - 06/31/2006 4 Between 07/01/2006 - 09/31/2006 5 Greater than 10/01/2006 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use SUMPRODUCT with a Date
Hi!
1. Less than 01/01/2006 5 Greater than 10/01/2006 Those 2 ranges are pretty wide open! Create a small 2 column table: A1 = 0 | B1 = 12/31/2005 A2 = 1/1/2006 | B2 = 3/31/2006 A3 = 4/1/2006 | B3 = 6/30/2006 A4 = 7/1/2006 | B4 = 9/30/2206 A5 = 10/1/2006 | B5 = 100000 Then you can just refer to the appropriate range of cells: =SUMPRODUCT(--($X$2:$X$2000=A1),--($X$2:$X$2000<=B1),--($AD$2:$AD$2000="BRENNAN JR")) Biff "msbutton27" wrote in message ... Hey all, I have a SUMPRODUCT equation (below) in which I want to look for a specific name and then check the date column to see if the date is in Q1, Q2, Q3, etc... This is what I have: =SUMPRODUCT(--('Laptops - All'!$X$2:$X$2000"01/01/2006"),--('Laptops - All'!$AD$2:$AD$2000="BRENNAN JR")) Not working for me, returns all 0's - what can I do to specify 1. Less than 01/01/2006 2. Between 01/01/2006 - 03/31/2006 3. Between 04/01/2006 - 06/31/2006 4 Between 07/01/2006 - 09/31/2006 5 Greater than 10/01/2006 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can a date be used for conditional formatting? | Excel Worksheet Functions | |||
How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
Using sumproduct to count number by date | Excel Worksheet Functions | |||
Date Math Problem | Excel Worksheet Functions | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions |