SUMPRODUCT

  • Thread starter Thread starter Guest
  • Start date Start date
Very few contributors will download a file from an unknown source, so
you might not get much help. Can you describe what it is you are
trying to do and give examples of your formula - explain how it is not
working (error, wrong result etc)

Hope this helps.

Pete
 
Dear Pete - thank you for your reply.

The sourse in Sheet 1 (Download) says:
A B C
000001 00001001 -32644.52

The result in Sheet 2 says:
A B C
000001 00001001 =SUMPRODUCT(--(Download!A:A=A2)*(Download!B:B=B2)*(Download!C:C))

Hope this is not too confusing.

Regards
Gennaro
 
You can't use whole columns in SUMPRODUCT() functions. Use a smaller range
which is bigger then you will ever use but still short of a full column.

--
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
 
I haven't looked at your file, but ... at first glance ... unless you're
using XL07, your formula *cannot* contain entire column references (A:A,
B:B, ...etc.).

Start off by changing that in your formula to realistic range sizes, and see
what happens.
 
Thank you to Sandy and Ragdyer. I've now changed the range sizes and the
result returned by the formula is equal to #VALUE!

Not sure what elase i can possibly try!

Regards
Gennaro
 
Besides what has been said, if you want to count use
=SUMPRODUCT((Download!A2:A22=A2)*(Download!B2:B22=B2)*(Download!C2:C22))
to SUM c based on a & b use
=SUMPRODUCT((Download!A2:A22=A2)*(Download!B2:B22=B2)*Download!C2:C22)
 
Usually when I get #VALUE! errors it is because I am trying to do maths on
text. Do you have text in any of your ranges. If they look like numbers
you can test with =ISTEXT(A1) and copy down & across.

--
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
 
Using the asterisk form of Sumproduct,

=SUMPRODUCT((Download!A1:A100=A2)*(Download!B1:B100=B2)*Download!C1:C100)

*ANY* alpha characters in the calculating range (Column C), will cause the
#Value! error, BUT ... any text numbers will *still* calculate *accurately*.
If it looks like a number, the asterisk form will calculate it.
Also, if you are populating your calc range (Column C) with formulas that
equate to a zero length string ( "" ), this will also cause the #Value!
error.

You can try revising your formula to the unary form,

=SUMPRODUCT(--(Download!A1:A100=A2),--(Download!B1:B100=B2),Download!C1:C100
)

which will by-pass alpha and null entries.

Note, the unary form will by-pass text numbers also with no notification.
It will just calculate *true XL* numbers only.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Gennaro said:
Thank you to Sandy and Ragdyer. I've now changed the range sizes and the
result returned by the formula is equal to #VALUE!

Not sure what elase i can possibly try!

Regards
Gennaro

Ragdyer said:
I haven't looked at your file, but ... at first glance ... unless you're
using XL07, your formula *cannot* contain entire column references (A:A,
B:B, ...etc.).

Start off by changing that in your formula to realistic range sizes, and see
what happens.

--
Regards,

RD

--------------------------------------------------------------------------
Click to expand...
-
Please keep all correspondence within the NewsGroup, so all may benefit
Click to expand...
!
--------------------------------------------------------------------------
Click to expand...
-
Gennaro said:
Dear Pete - thank you for your reply.

The sourse in Sheet 1 (Download) says:
A B C
000001 00001001 -32644.52

The result in Sheet 2 says:
A B C
000001 00001001 =SUMPRODUCT(--(Download!A:A=A2)*(Download!B:B=B2)*(Download!C:C))

Hope this is not too confusing.

Regards
Gennaro
:

Very few contributors will download a file from an unknown source, so
you might not get much help. Can you describe what it is you are
trying to do and give examples of your formula - explain how it is not
working (error, wrong result etc)

Hope this helps.

Pete

Click to expand...
Click to expand...
Click to expand...
 
Thank you Sandy - How shall i insert the "ISTEXT" fomula within SUMPRODUCT
formula? I'm not sure how to link both formula.

Thank you again, Gennaro
 
Hey Don, some typos?

Both formulas are identical as far as calculations are concerned.
Both will total Column C.

Counting might be:

=SUMPRODUCT((Download!A2:A22=A2)*(Download!B2:B22=B2)*(Download!C2:C22<>""))
OR, simply:
=SUMPRODUCT((Download!A2:A22=A2)*(Download!B2:B22=B2))
 
Dear Ragdyer - Thank you for your help. Your latest suggestion does work but
somehow it’s not quite right. Can you be kind and have a look at the file
i've sent via email? It would be greatly appreciated.

Regards
Gennaro


Ragdyer said:
Using the asterisk form of Sumproduct,

=SUMPRODUCT((Download!A1:A100=A2)*(Download!B1:B100=B2)*Download!C1:C100)

*ANY* alpha characters in the calculating range (Column C), will cause the
#Value! error, BUT ... any text numbers will *still* calculate *accurately*.
If it looks like a number, the asterisk form will calculate it.
Also, if you are populating your calc range (Column C) with formulas that
equate to a zero length string ( "" ), this will also cause the #Value!
error.

You can try revising your formula to the unary form,

=SUMPRODUCT(--(Download!A1:A100=A2),--(Download!B1:B100=B2),Download!C1:C100
)

which will by-pass alpha and null entries.

Note, the unary form will by-pass text numbers also with no notification.
It will just calculate *true XL* numbers only.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Gennaro said:
Thank you to Sandy and Ragdyer. I've now changed the range sizes and the
result returned by the formula is equal to #VALUE!

Not sure what elase i can possibly try!

Regards
Gennaro

Ragdyer said:
I haven't looked at your file, but ... at first glance ... unless you're
using XL07, your formula *cannot* contain entire column references (A:A,
B:B, ...etc.).

Start off by changing that in your formula to realistic range sizes, and see
what happens.

--
Regards,

RD

--------------------------------------------------------------------------
Click to expand...
-
Please keep all correspondence within the NewsGroup, so all may benefit
Click to expand...
!
--------------------------------------------------------------------------
Click to expand...
-
Click to expand...
Click to expand...
 
Maybe you mean in C2 in Sheet2:
=SUMPRODUCT((Download!$A$2:$A$3824=$A2)*(Download!$B$2:$B$3824=$B2),Download!C$2:C$3824)
with C2 then copied across and filled down to populate

---
 
=SUMPRODUCT(--(Download!A2:A4000=Sheet2!A2),--(Download!B2:B4000=B2),(Download!C2:C4000))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Max - Thank you very very much. You have been once again very helpful.

Kind regards
Gennaro
 
Besides the fact that I have not received any e-mails on this subject, it's
usually standard procedure in these groups to *first* ask permission,
especially since my signature includes a statement to the fact of keeping
the discussions public.

However, if you wish, you can send a *small* file.
Just cut out cutout from my address.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Gennaro said:
Dear Ragdyer - Thank you for your help. Your latest suggestion does work
but
somehow it's not quite right. Can you be kind and have a look at the file
i've sent via email? It would be greatly appreciated.

Regards
Gennaro
Click to expand...
 
Back
Top