I am trying to use the SUMPRODUCT formula in the following format:

SUMPRODUCT(--(....),--(....),()) to look up 2 conditions and sum the product of those.

I have 4 different tabs that are my raw data to look up against - on 3 of the tabs the formula works fine. On the fourth tab, it gives a #N/A error and I have no idea why. The data is pretty similar on all tabs, so I cannot possibly pinpoint why it would work on one and not the other.

Here's an example:

=SUMPRODUCT(

--('New Sales-YTD'!$AD$3:$AD$64000=$C159),

--('New Sales-YTD'!$G$3:$G$64000=$B159),

('New Sales-YTD'!$T$3:$T$64000))

it seems pretty straightforward:

- the range AD contains date information

- the range G contains market information

- the range T is the sales value

The only difference that I can see between the tabs where the formula works and the one that it does not work on is that the last tab has negative values in the T range? Other than that it is all the same stuff...

When I try to do a SUMIF or VLOOKUP I get no errors. The #NA error only shows up with SUMPRODUCT...

Any ideas ??

Thanks.