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
|