You will also get a #N/A! error if your any ranges are not of the same size
in the SUMPRODUCT() formula. Have you checked them?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Kate" wrote in message
...
Thanks Dave for your speedy reply. I have checked the data and there
aren't
any #N/A's. The data doesn't have any hidden rows/columns or formulas.
It
is extracted from a database and sent to us by a third party - have never
had
any problems with it before!!

"Dave Peterson" wrote:

Any chance you have those #n/a errors in your data?

Remember to look at hidden rows, too -- autofilter or manually hidden.

Kate wrote:

Please help - I have spent 4 hrs trying to work out where I have gone
wrong
with this!

I have a large set of data on a tab called 'data'. It is over 60
columns by
523 rows. I would like to do some counts based on what is in 3 columns
of
data. A section of it is shown below:

Lead Type Name Closed Reason
Saleslinq Charlotte Fryer Existing Customer
Redpoint Debbie Storr Not Interested
Exhibition Nigel Aldridge No to Company
Saleslinq Debbie Storr Existing Customer
Exhibition Charlotte Fryer Not Interested

There are several columns of data inbetween the above I have shown. I
would
like to do a sum to find out how many rows of data have a lead type of
'Saleslinq', the name of 'Debbie Storr' and the Closed Reason of
'Existing
Customer'. I would expect to get a result from the above of '1'

I have used the following formula
=SUMPRODUCT(--(Data!R2:R523="Debbie
Storr-CDM"),--(Data!V2:V523="Existing
Customer"),--(Data!DB2:DB523="Saleslinq"))

Week on week, I get new data and paste over and have never had a
problem
with this formula. However, this week, all my sums have turned to
'#N/A'. I
have checked the formatting of the data, done some text to columns just
to be
sure and it still doesn't give me a result.

When I try to use the conditional sum wizard it tells me the formula I
have
is too long as I have too many criterias.

Thanks
Kate

--

Dave Peterson