SUMPRODUCT or SUMIF or ...

[Guest]

Hi,
I'm working in a worksheet that has:
A B C D H
1 8 5 7 6
2 EXX GAL SHE EXX ... EXX
3 25 30 20 15 290
4 21 45 30 12 240
5 45 36 21 36 576

I need to put in the H column:
(25*8)+(15*6)= 290 with the criteria EXX
(21*8)+(12*6)= 240 "

I think that I should use a SUMPRODUCT or a SUMIF but I don't know how
Can you help with this?? Thanks

 

[Max]

One way ..

Put in H3:
=SUMPRODUCT(--(A$2$2=H$2),(A33)*(A$1$1))

Copy down

 

[Guest]

Thanks for your reply!
According to your formula H3 = 0 (don't understand why??)

Note: between (A$2$2=H$2) and (A33), my excell doesn't let me put a ,
(comma) I had to put ; .


"Max" escreveu:

 

[Bernard Liengme]

Perhaps the text entries are not exactly the same; maybe one has 2 spaces
before it and another has 3.
In J2 use this formula =A2=H2. This should return TRUE if the text entries
are exactly the same.
best wishes

 

[Max]

Thanks for your reply!
According to your formula H3 = 0 (don't understand why??)
Note: between (A$2$2=H$2) and (A33),
my excell doesn't let me put a , (comma) I had to put ;

Perhaps try opening this sample file with the implemented formula which does
return the results you indicated in your post:
http://flypicture.com/p.cfm?id=52598

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: nfbelo_wksht_1.xls

(Think the conversion to your excel version should be automatic .. )

And if you still get zero in H3, as Bernard pointed out, there's every
possibility of "invisible" extraneous spaces somewhere within the cells with
the text "EXX", "GAL", etc, which might be throwing the matching off, so we
could try with a TRIM wrapped around the range A22 and H2, i.e. put
instead in H3:
=SUMPRODUCT(--(TRIM(A$2$2)=TRIM(H$2)),(A33)*(A$1$1))
and copy H3 down as before ..

Let us know how it went for you !