Hi,
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.
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.