New Errors in Sumproduct Usage

[Guest]

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

 

[Guest]

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

 

[Bernie Deitrick]

D Mouse...

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

HTH,
Bernie
MS Excel MVP

 

[Guest]

The formula looks like this:

SUMPRODUCT(((Detail!$B$2:$B$65536=Summary!$B15)*(Detail!$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

 

[Bob Phillips]

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,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)

 

[Guest]

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!!!!