hlp with sumproduct!

  • Thread starter Thread starter via135
  • Start date Start date
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
 
Hi!

Try this:

=SUMIF(H1:H5,H1,I1:I5)+SUMIF(Sheet7!H1:H5,H1,Sheet7!I1:I5)

Biff
 
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.
 
thks Biff!

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

-via135
 
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
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional Time Calculation 10
Highlight row 3
Vendor List 6
To combine various cell formulae in to the final cell 1
Adding cells but leaving blank if not populated 1
Testing for a consistent value counting blanks 1
How to make Horizontal data go Vertical 10
Need Assist With Formula Creation 3

Back
Top