SUMPRODUCT Error

  • Thread starter Thread starter Kate
  • Start date Start date
K

Kate

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
 
Apologies - I should have said that each name in my data has some letters
after it, hence 'Debbie Storr-CDM' in the formula (I should have added this
to my example below)
 
Any chance you have those #n/a errors in your data?

Remember to look at hidden rows, too -- autofilter or manually hidden.
 
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!!
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Kate said:
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!!
Click to expand...
 
Back
Top