Hi,

I'm using Excel 2003. This formula works in all worksheets except for 2
(note name of worksheet in formula is changed for specific worksheet)

=SUMPRODUCT(--(Memphis!$F$4:$F$200<TODAY()),--(Memphis!$I$4:$I$200<0),--Memphis!$I$4:$I$200)

The 2 worksheets that this formula doesn't work on have the smallest range.
I want to use a consistent range in the formula and not have to change base
on the number of rows each worksheet may have.

Any advise on what could be wrong.

TIA

The worksheets that it doesn't work have some other type data in the cells.
For the formula to work in the sheets with the smallest ranges you need to
remove the extra data. All rows of data up to 200 should only have the table
you are looking at or have empty cells.

"Jan" wrote:

Hi,

I'm using Excel 2003. This formula works in all worksheets except for 2
(note name of worksheet in formula is changed for specific worksheet)

=SUMPRODUCT(--(Memphis!$F$4:$F$200<TODAY()),--(Memphis!$I$4:$I$200<0),--Memphis!$I$4:$I$200)

The 2 worksheets that this formula doesn't work on have the smallest range.
I want to use a consistent range in the formula and not have to change base
on the number of rows each worksheet may have.

Any advise on what could be wrong.

TIA

Update. I noticed that the worksheet where the formula doesn't work is
including the value in the total row. But if that is the case, why wouldn't
it include the total row of the other worksheets? The total row does not
have a date value in the "F" column.

"Jan" wrote:

Hi,

I'm using Excel 2003. This formula works in all worksheets except for 2
(note name of worksheet in formula is changed for specific worksheet)

=SUMPRODUCT(--(Memphis!$F$4:$F$200<TODAY()),--(Memphis!$I$4:$I$200<0),--Memphis!$I$4:$I$200)

The 2 worksheets that this formula doesn't work on have the smallest range.
I want to use a consistent range in the formula and not have to change base
on the number of rows each worksheet may have.

Any advise on what could be wrong.

TIA

An empty cell is treated as 0. Dates are also numbers just format to show
time.

Memphis!$F$4:$F$200<TODAY() is really
0 < 39902

"Jan" wrote:

Update. I noticed that the worksheet where the formula doesn't work is
including the value in the total row. But if that is the case, why wouldn't
it include the total row of the other worksheets? The total row does not
have a date value in the "F" column.

"Jan" wrote:

Hi,

I'm using Excel 2003. This formula works in all worksheets except for 2
(note name of worksheet in formula is changed for specific worksheet)

=SUMPRODUCT(--(Memphis!$F$4:$F$200<TODAY()),--(Memphis!$I$4:$I$200<0),--Memphis!$I$4:$I$200)

The 2 worksheets that this formula doesn't work on have the smallest range.
I want to use a consistent range in the formula and not have to change base
on the number of rows each worksheet may have.

Any advise on what could be wrong.

TIA