New Errors in Sumproduct Usage
 

Hi everyone...I've been using the Sumproduct function for quite a while to
extract either the count of multiple criterias or the sum of multiple
criterias. I have a standard report I've been maintaining for a couple of
months now using the function.

When I attempted to update my report today, I get a #N/A error message for
all my formulas. I've checked my Add-ins to see if anything has been
deselected. And, I've recreated one of the formulas in a clean spreadsheet,
but I still get the message.

Is anyone else encountering this problem? And if so, has there been any
solutions?

Thanks in advance...
--
Danger Mouse

New Errors in Sumproduct Usage
 

is it an #N/A message or the #N/A output in the equation?
Check each array for an #N/A in the array Sumproduct will output an error if
any of the appropriate arrays has an error

"Danger Mouse" wrote:

Hi everyone...I've been using the Sumproduct function for quite a while to
extract either the count of multiple criterias or the sum of multiple
criterias. I have a standard report I've been maintaining for a couple of
months now using the function.

When I attempted to update my report today, I get a #N/A error message for
all my formulas. I've checked my Add-ins to see if anything has been
deselected. And, I've recreated one of the formulas in a clean spreadsheet,
but I still get the message.

Is anyone else encountering this problem? And if so, has there been any
solutions?

Thanks in advance...
--
Danger Mouse

New Errors in Sumproduct Usage
 

D Mouse...

What is the formula? And is it expecting numbers and getting strings, perhaps?

HTH,
Bernie
MS Excel MVP


"Danger Mouse" wrote in message
...
Hi everyone...I've been using the Sumproduct function for quite a while to
extract either the count of multiple criterias or the sum of multiple
criterias. I have a standard report I've been maintaining for a couple of
months now using the function.

When I attempted to update my report today, I get a #N/A error message for
all my formulas. I've checked my Add-ins to see if anything has been
deselected. And, I've recreated one of the formulas in a clean spreadsheet,
but I still get the message.

Is anyone else encountering this problem? And if so, has there been any
solutions?

Thanks in advance...
--
Danger Mouse

New Errors in Sumproduct Usage
 

The formula looks like this:

SUMPRODUCT(((Detail!$B$2:$B$65536=Summary!$B15)*(D etail!$I$2:$I$65536=Summary!H$3)))

Column B contains the names of collectors cell $B15 is the name of the
collector to be found in that column, and
Column I contains the names of areas/marketplaces and cell H$3 has the name
of the marketplace to be found

The formula has always worked before. The only thing I did was update the
information found in the Detail tab of the spreadsheet. I've used the report
for about 3 months now and this is the first time I'm getting this error.

It should provide me a number that reflects the number of times Jane Doe has
New York as an area/marketplace.

Thanks again...dm
--
Danger Mouse


"Bernie Deitrick" wrote:

D Mouse...

What is the formula? And is it expecting numbers and getting strings, perhaps?

HTH,
Bernie
MS Excel MVP


"Danger Mouse" wrote in message
...
Hi everyone...I've been using the Sumproduct function for quite a while to
extract either the count of multiple criterias or the sum of multiple
criterias. I have a standard report I've been maintaining for a couple of
months now using the function.

When I attempted to update my report today, I get a #N/A error message for
all my formulas. I've checked my Add-ins to see if anything has been
deselected. And, I've recreated one of the formulas in a clean spreadsheet,
but I still get the message.

Is anyone else encountering this problem? And if so, has there been any
solutions?

Thanks in advance...
--
Danger Mouse

New Errors in Sumproduct Usage
 

That error usually occurs when the ranges are differently sized (not so in
your example), or one of the cells = #N/A

Run this to see

=COUNTIF(B:B,NA())

and

=COUNTIF(D:D,NA())

if either doesn't return 0, the problem is in that column (maybe from a
lookup formula).

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Danger Mouse" wrote in message
...
The formula looks like this:

SUMPRODUCT(((Detail!$B$2:$B$65536=Summary!$B15)*(D etail!$I$2:$I$65536=Summary!H$3)))

Column B contains the names of collectors cell $B15 is the name of the
collector to be found in that column, and
Column I contains the names of areas/marketplaces and cell H$3 has the
name
of the marketplace to be found

The formula has always worked before. The only thing I did was update the
information found in the Detail tab of the spreadsheet. I've used the
report
for about 3 months now and this is the first time I'm getting this error.

It should provide me a number that reflects the number of times Jane Doe
has
New York as an area/marketplace.

Thanks again...dm
--
Danger Mouse


"Bernie Deitrick" wrote:

D Mouse...

What is the formula? And is it expecting numbers and getting strings,
perhaps?

HTH,
Bernie
MS Excel MVP


"Danger Mouse" wrote in message
...
Hi everyone...I've been using the Sumproduct function for quite a while
to
extract either the count of multiple criterias or the sum of multiple
criterias. I have a standard report I've been maintaining for a couple
of
months now using the function.

When I attempted to update my report today, I get a #N/A error message
for
all my formulas. I've checked my Add-ins to see if anything has been
deselected. And, I've recreated one of the formulas in a clean
spreadsheet,
but I still get the message.

Is anyone else encountering this problem? And if so, has there been any
solutions?

Thanks in advance...
--
Danger Mouse

New Errors in Sumproduct Usage
 

Thanks everyone...ur all awesome!!! It appears the range sizes have changed
from the previous two months that I've used the spreadsheet.....

You ALL rock!!!!
--
Danger Mouse


"Bob Phillips" wrote:

That error usually occurs when the ranges are differently sized (not so in
your example), or one of the cells = #N/A

Run this to see

=COUNTIF(B:B,NA())

and

=COUNTIF(D:D,NA())

if either doesn't return 0, the problem is in that column (maybe from a
lookup formula).

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Danger Mouse" wrote in message
...
The formula looks like this:

SUMPRODUCT(((Detail!$B$2:$B$65536=Summary!$B15)*(D etail!$I$2:$I$65536=Summary!H$3)))

Column B contains the names of collectors cell $B15 is the name of the
collector to be found in that column, and
Column I contains the names of areas/marketplaces and cell H$3 has the
name
of the marketplace to be found

The formula has always worked before. The only thing I did was update the
information found in the Detail tab of the spreadsheet. I've used the
report
for about 3 months now and this is the first time I'm getting this error.

It should provide me a number that reflects the number of times Jane Doe
has
New York as an area/marketplace.

Thanks again...dm
--
Danger Mouse


"Bernie Deitrick" wrote:

D Mouse...

What is the formula? And is it expecting numbers and getting strings,
perhaps?

HTH,
Bernie
MS Excel MVP


"Danger Mouse" wrote in message
...
Hi everyone...I've been using the Sumproduct function for quite a while
to
extract either the count of multiple criterias or the sum of multiple
criterias. I have a standard report I've been maintaining for a couple
of
months now using the function.

When I attempted to update my report today, I get a #N/A error message
for
all my formulas. I've checked my Add-ins to see if anything has been
deselected. And, I've recreated one of the formulas in a clean
spreadsheet,
but I still get the message.

Is anyone else encountering this problem? And if so, has there been any
solutions?

Thanks in advance...
--
Danger Mouse