=SUMPRODUCT(--not(iserror('Retail 2006'!$AE$2:$AE$4000=$D$2),('Retail
2006'!$AE$2:$AE$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail
2006'!$F$2:$F$4000)

Did you test that formula?

Aside from the syntax error it still won't work:

('Retail 2006'!$AE$2:$AE$4000=$D$2)

That array will still fail on any errors.

Biff

"bj" wrote in message
...
I tried to respond yesterday, but got one of those "No such site messages"?
try
=SUMPRODUCT(--not(iserror('Retail 2006'!$AE$2:$AE$4000=$D$2),('Retail
2006'!$AE$2:$AE$4000=$D$2)*('Retail 2006'!$C$2:$C$4000<=$N$2),'Retail
2006'!$F$2:$F$4000)

"R Ormerod" wrote:

This is not practicable because:
a) The data worksheet is an import from a text file which is regularly
refreshed
b) The formula referring to column C specifies less than or equal to the
value in N2 (for YTD sales)

Something else - column AE is the last column of data in Retai 2006 and
is
VLOOKUP formulae (the rest of the data is values). There are #N/A values
in
this column.

--
R Ormerod


"bj" wrote:

on your retail 2006 sheet select all
data-filter-auto filter
in column AE filter select
custom type in the value for D$2
in column C filter select custom type in the value for N2
check column F for a #NA


"R Ormerod" wrote:

The following formula is returning the #N/A error:

=SUMPRODUCT(('Retail 2006'!$AE$2:$AE$4000=$D$2)*('Retail
2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000)

The following formula in other worksheets in the same file is
working:

=SUMPRODUCT(('Retail 2006'!$E$2:$E$4000=$D$2)*('Retail
2006'!$C$2:$C$4000<=$N$2),'Retail 2006'!$F$2:$F$4000)

The only difference is the first argument, which is referring to a
column
containing store types (Department Stores, Chains etc.) in the
worksheet that
isn't working and store codes (C10, C20, C30 etc.) in the one that
is. Cell
formats are set to general for both columns.

For your information:

Column C contains months of the year

Column F contains sales

Cells D2 & N2 are linked to combo boxes and display the Store Type
and Month
respectively. Again, cell formats are General.

What could be going wrong?

Regards

--
R Ormerod