SUMPRODUCT or SUMIF or ...

N

nfbelo

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
 
N

nfbelo

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

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


"Max" escreveu:
 
B

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
 
M

Max

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

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 A2:D2 and H2, i.e. put
instead in H3:
=SUMPRODUCT(--(TRIM(A$2:D$2)=TRIM(H$2)),(A3:D3)*(A$1:D$1))
and copy H3 down as before ..

Let us know how it went for you !
 
Top