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

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

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