SUMPRODUCT

P

Pete_UK

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
 
G

Gennaro

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
 
S

Sandy Mann

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
R

Ragdyer

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.
 
G

Gennaro

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
 
D

Don Guillett

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)
 
S

Sandy Mann

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
R

Ragdyer

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...
 
G

Gennaro

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
 
R

Ragdyer

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))
 
G

Gennaro

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...
 
M

Max

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
 
B

Bob Phillips

=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)
 
G

Gennaro

Max - Thank you very very much. You have been once again very helpful.

Kind regards
Gennaro
 
R

RagDyer

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...
 
Top