Help with SUMPRODUCT

[lukus2005]

I was trying to set up a PivotTable and not having much success with
it and it was suggested that I try the SUMPRODUCT function. I did but
it returns a #VALUE! error.

Each rows of my spreadsheet contains a specific project that was
completed.
In column C i have the name of the client who's name may come up
several time throughout my spreadsheet.
In column M i have the net profit for that specific project.

It is my understanding that I can use the SUMPRODUCT function to add
all the profit amounts for all the projects completed for a specific
client. So for example, I want to add all the profits (Column M) for
all the projects completed for a client named "ACME" (Column C). This
is the formula I tried but gave me an error...

=SUMPRODUCT((M17:M208)*($C$17:$C$208="ACME"))

 

[John]

I tested your function and it works for me.But if column M are not numbers,
it will return... #Value error
HTH
John

 

[joeu2004]

[the following] returns a #VALUE! error.
[....]
=SUMPRODUCT((M17:M208)*($C$17:$C$208="ACME"))

My guess: some of the M cells contains the null string, such as the
false part of =if(condition,something,""). Try the following:

=SUMPRODUCT(N(M17:M208)*($C$17:$C$208="ACME"))

 

[lukus2005]

[the following] returns a #VALUE! error.
[....]
=SUMPRODUCT((M17:M208)*($C$17:$C$208="ACME"))

My guess:  some of the M cells contains the null string, such as the
false part of =if(condition,something,"").  Try the following:

=SUMPRODUCT(N(M17:M208)*($C$17:$C$208="ACME"))

You were right... adding the N to the formula fixed the problem.
However, I did a Data Sort and the formula now only gives my $0.00
instead of the amount that was showing when i first added the N to the
formula. Not sure why that occured.

 

[Bob Phillips]

If you only have one condition, use SUMIF not SUMPRODUCT

=SUMIF($C$17:$C$208,"ACME",M17:M208)

--
__________________________________
HTH

Bob

[the following] returns a #VALUE! error.
[....]
=SUMPRODUCT((M17:M208)*($C$17:$C$208="ACME"))

My guess: some of the M cells contains the null string, such as the
false part of =if(condition,something,""). Try the following:

=SUMPRODUCT(N(M17:M208)*($C$17:$C$208="ACME"))

You were right... adding the N to the formula fixed the problem.
However, I did a Data Sort and the formula now only gives my $0.00
instead of the amount that was showing when i first added the N to the
formula. Not sure why that occured.

 

[Bob Phillips]

The $ has absolutely no impact on a single formula.

--
__________________________________
HTH

Bob

Enter your formula into a blank cell and Drop the $ from your range ie.

=SUMPRODUCT((M17:M208)*(C17:C208 ="ACME"))

Regards
Fred

[the following] returns a #VALUE! error.
[....]
=SUMPRODUCT((M17:M208)*($C$17:$C$208="ACME"))

My guess: some of the M cells contains the null string, such as the
false part of =if(condition,something,""). Try the following:

=SUMPRODUCT(N(M17:M208)*($C$17:$C$208="ACME"))

You were right... adding the N to the formula fixed the problem.
However, I did a Data Sort and the formula now only gives my $0.00
instead of the amount that was showing when i first added the N to the
formula. Not sure why that occured.

 

[Shane Devenshire]

Hi,

Now that you have found a data integrity problem you should go back an visit
the pivot table solution, it should work just fine.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[lukus2005]

Hi,

Now that you have found a data integrity problem you should go back an visit
the pivot table solution, it should work just fine.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

Not sure what you mean by "data integrity" since all results in that
columns are accurate, even though some might be empty since they are
on-going project and not yet completed.

Now the only thing I can see is that those "empty" cells are the
results of a condition not being met ie: =IF((B9+D9)=E9,(E9-F9),"")
and thus, those empty cells may not be interpreted as a numerical
value so this could be the cause of my problem. If so, how do i fix
that problem? I could change the "" to "0" in my formula but I want
the cells to remain empty and not show a zero.

 

[Peo Sjoblom]

You already received an answer by Bob Phillips


=SUMIF($C$17:$C$208,"ACME",$M$17:$M$208)


No need for SUMPRODUCT in this case.

If you have more than one criteria than it is time to use SUMPRODUCT but
not the way you used it if you want to have "" in the cells to show as a
blank

=SUMPRODUCT(--($C$17:$C$208="ACME"),$M$17:$M$208)

that way you won't get VALUE errors, having said that if you only have
one criteria use SUMIF, it is much faster



--


Regards,


Peo Sjoblom

Hi,

Now that you have found a data integrity problem you should go back an
visit
the pivot table solution, it should work just fine.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

Not sure what you mean by "data integrity" since all results in that
columns are accurate, even though some might be empty since they are
on-going project and not yet completed.

Now the only thing I can see is that those "empty" cells are the
results of a condition not being met ie: =IF((B9+D9)=E9,(E9-F9),"")
and thus, those empty cells may not be interpreted as a numerical
value so this could be the cause of my problem. If so, how do i fix
that problem? I could change the "" to "0" in my formula but I want
the cells to remain empty and not show a zero.

 

[lukus2005]

Not sure what you mean by "data integrity" since all results in that
columns are accurate, even though some might be empty since they are
on-going project and not yet completed.

Now the only thing I can see is that those "empty" cells are the
results of a condition not being met ie: =IF((B9+D9)=E9,(E9-F9),"")
and thus, those empty cells may not be interpreted as a numerical
value so this could be the cause of my problem.  If so, how do i fix
that problem?  I could change the "" to "0" in my formula but I want
the cells to remain empty and not show a zero.

Now I am even more at a loss. Each time I sort my data differently,
my total changes. All of which are incorrect, incidently.

The formula that I am using is based on tips I gathered on here...
=SUMPRODUCT(N(M16:M207)*(C16:C207="ACME"))... removing the N produces
a #VALUE! error. I tried using a different client names but the totals
are still inaccurate.

 

[Peo Sjoblom]

You don't need to use SUMPRODUCT in this case

--


Regards,


Peo Sjoblom

Not sure what you mean by "data integrity" since all results in that
columns are accurate, even though some might be empty since they are
on-going project and not yet completed.

Now the only thing I can see is that those "empty" cells are the
results of a condition not being met ie: =IF((B9+D9)=E9,(E9-F9),"")
and thus, those empty cells may not be interpreted as a numerical
value so this could be the cause of my problem. If so, how do i fix
that problem? I could change the "" to "0" in my formula but I want
the cells to remain empty and not show a zero.

Now I am even more at a loss. Each time I sort my data differently,
my total changes. All of which are incorrect, incidently.

The formula that I am using is based on tips I gathered on here...
=SUMPRODUCT(N(M16:M207)*(C16:C207="ACME"))... removing the N produces
a #VALUE! error. I tried using a different client names but the totals
are still inaccurate.

 

[lukus2005]

You already received an answer by Bob Phillips

=SUMIF($C$17:$C$208,"ACME",$M$17:$M$208)

No need for SUMPRODUCT in this case.

If you have more than one criteria than it is time to use SUMPRODUCT but
not the way you used it if you want to have "" in the cells to show as a
blank

=SUMPRODUCT(--($C$17:$C$208="ACME"),$M$17:$M$208)

that way you won't get VALUE errors, having said that if you only have
one criteria use SUMIF, it is much faster

--

Regards,

Peo Sjoblom

Thanks Peo, your formula did the trick, though I'm not clear on the
use of "--".

By the way, the reason why I am using SUMPRODUCT with just one
criteria is because I am experimenting with it as I plan on using it
with more than one criteria once I am comfortable with its use.

 

[lukus2005]

Hi,

Now that you have found a data integrity problem you should go back an visit
the pivot table solution, it should work just fine.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

PivotTable is still a no go. Why am I not able to create one? The
error that pops up says...

"The PivotTable field is not valid. To create a PivotTable report, you
must use data that is organised with labeled columns."

All of my columns are labeled. You mention data integrity, can you
elaborate on that. As explained previously, the only problem I can see
being wrong with my data is the fact that some cell values are ""
instead of a numerical value. Could this be the source of my problems?

 

[lukus2005]

PivotTable is still a no go.  Why am I not able to create one?  The
error that pops up says...

"The PivotTable field is not valid. To create a PivotTable report, you
must use data that is organised with labeled columns."

All of my columns are labeled. You mention data integrity, can you
elaborate on that. As explained previously, the only problem I can see
being wrong with my data is the fact that some cell values are ""
instead of a numerical value.  Could this be the source of my problems?

Just to let everyone know... I solved the data integrity issue.

Problem was that Client's name in Column A was overlapping onto Column
B which had no heading as it was used for the overlapping purpose. I
deleted Column B and simply made Column A wider, thus leaving no
columns without a header.

I had previously tried merging columns A and B together but it would
seem Excel still treats that as 2 separate columns, therefore, Column
B appeared to remain header-less.

 

[joeu2004]

You were right... adding the N to the formula fixed the problem.
However, I did a Data Sort and the formula now only gives my $0.00
instead of the amount that was showing when i first added the N to
the formula.  Not sure why that occured.

Just a guess: you did not include C17:C208 when you sorted M17:M208,
or vice versa.

Generally, sorting has no impact on dependent formulas. Think of
sorting as copying the range to be sorted somewhere else, sorting,
then copying the sorted range over the original range.

In any case, whatever your problem is, it is likely has nothing to do
with the use of the N() function, which you should continue to use,
and the absolute or relative references (with and without the "$").

 

[joeu2004]

The $ has absolutely no impact on a single formula.

Cute! I suspect there was no pun intended.

 

[Bob Phillips]

You'll have to explain that one to me.

--
__________________________________
HTH

Bob

The $ has absolutely no impact on a single formula.

Cute! I suspect there was no pun intended.