hlp with sumproduct!

V

via135

hi!

what's wrong with the following formula which throws #VALUE error?

=SUMPRODUCT(--(H1:H5,H1)*(--(Sheet7!H1:H5,H1)),(--(I1:I5),(Sheet7!I1:I5)))

-via135
 
A

Arvi Laanemets

Hi

Probably the main reason is --(Range,Value) part - there must
be --(Range=Value) instead.

Additionally you use 2 different syntaxis at same time - it doesn't cause an
error, but you don't gain anything too. You can have SUMPRODUCT or in form
=SUMPRODUCT((Range1=Value2)*(Range2=Value2)*...*(RangeN))
or in form
=SUMPRODUCT(--(Range1=Value2),--(Range2=Value2),...,RangeN)

So, your formula probably will be
=SUMPRODUCT(--(H1:H5=H1),--(Sheet7!H1:H5=H1)),I1:I5,Sheet7!I1:I5)

The formula will be multiply row-wise values in ranges I1:I5 and
Sheet7!I1:I5 and, when according values in both ranges H1:H5 and
Sheet7!H1:H5 equal with value in H1, and sum then all multiplied values.
When this is not what you intented, then you have to ask again.
 
V

via135

thks Biff!

your formula works nicely!!
can u pl explain why "someproduct" doesn't work for this situation?

-via135
 
B

Biff

Hi!

I was guessing that the Sumif is what you intended.

You had the syntax of the Sumproduct formula wrong and Sumproduct wouldn't
do what you had intended to do.

See Arvi's reply.

Biff
 
V

via135

hi Biff!

i am stressing on SUMPRODUCT!
Arvi's formula gives me a wrong result for my following data!

Sheet7!H1:I5
CDA 10
EFG 30
ABC 40
BCD 50
ABC 60

Sheet8!H1:I5
ABC 10
BCD 50
ABC 30
CDA 70
ACB 60

i want the sum of all "ABC"!

while your SUMIF gives me the correct result of 140
Aarvi's SUMPRODUCT gives me 1400 as he explained it!

my point is whether it is possible to bring the result of 140 using
"sumproduct"?

thks!

-via135
 
Top