Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT is giving incorrect number
I am using Windows XP and Excel 2000.
I have 2 worksheets 1) Data 2) betwen_2_dates In the sheet between_2_dates the cell D2 has this date input 01/01/2007 In the sheet between_2_dates the cell G2 has this date input 31/12/2007 In the sheet between_2_dates the cell B2 has this formula =SUMPRODUCT(--(Data!$A$1:$A$10000=D2),--(Data!$A$1:$A$10000<=G2)) In the sheet Data column A has the info. In column A cell A1 of sheet Data there is the following text "MainDate". In sheet Data column A cells A2 to A134 there are dates which all fall between 01/04/2007 and 30/06/2007. However in sheet between_2_dates cell B2, the number that is displayed is 132. But the number should be 133. Any idea what is causing the formula to display 1 less than it should do? Many thanks for any help with this. -- Richard |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT is giving incorrect number
I suspect one of dates isn't! (Date format). I ran a simulation of your data
and got the answer of 133 when my dates were within your range in A2 to A134. If I change a cell to TEXT but it looks like a date, then I get an answer of 132. "Richard" wrote: I am using Windows XP and Excel 2000. I have 2 worksheets 1) Data 2) betwen_2_dates In the sheet between_2_dates the cell D2 has this date input 01/01/2007 In the sheet between_2_dates the cell G2 has this date input 31/12/2007 In the sheet between_2_dates the cell B2 has this formula =SUMPRODUCT(--(Data!$A$1:$A$10000=D2),--(Data!$A$1:$A$10000<=G2)) In the sheet Data column A has the info. In column A cell A1 of sheet Data there is the following text "MainDate". In sheet Data column A cells A2 to A134 there are dates which all fall between 01/04/2007 and 30/06/2007. However in sheet between_2_dates cell B2, the number that is displayed is 132. But the number should be 133. Any idea what is causing the formula to display 1 less than it should do? Many thanks for any help with this. -- Richard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT is giving incorrect number
Thank you Toppers
I had a look at the cell formats and they were ok. So I had a closer look at the dates and 1 date was input as the year 2207. So that solves my problem. -- Richard "Toppers" wrote: I suspect one of dates isn't! (Date format). I ran a simulation of your data and got the answer of 133 when my dates were within your range in A2 to A134. If I change a cell to TEXT but it looks like a date, then I get an answer of 132. "Richard" wrote: I am using Windows XP and Excel 2000. I have 2 worksheets 1) Data 2) betwen_2_dates In the sheet between_2_dates the cell D2 has this date input 01/01/2007 In the sheet between_2_dates the cell G2 has this date input 31/12/2007 In the sheet between_2_dates the cell B2 has this formula =SUMPRODUCT(--(Data!$A$1:$A$10000=D2),--(Data!$A$1:$A$10000<=G2)) In the sheet Data column A has the info. In column A cell A1 of sheet Data there is the following text "MainDate". In sheet Data column A cells A2 to A134 there are dates which all fall between 01/04/2007 and 30/06/2007. However in sheet between_2_dates cell B2, the number that is displayed is 132. But the number should be 133. Any idea what is causing the formula to display 1 less than it should do? Many thanks for any help with this. -- Richard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT - Giving me trouble | Excel Worksheet Functions | |||
SUMPRODUCT returning incorrect result | Excel Worksheet Functions | |||
Sumproduct giving #NA | Excel Worksheet Functions | |||
Very large workbook now giving incorrect results :( | Excel Discussion (Misc queries) | |||
Formula giving incorrect answer... | Excel Discussion (Misc queries) |