SUMPRODUCT Error
 

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

SUMPRODUCT Error
 

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)

"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

SUMPRODUCT Error
 

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

SUMPRODUCT Error
 

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

SUMPRODUCT Error
 

You say you GET new data. Perhaps this data has leading or trailing spaces?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kate" wrote in message
...
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

SUMPRODUCT Error
 

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