SUMPRODUCT or SUMIF or ...
 

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

One way ..

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

Copy down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"nfbelo" wrote in message
...
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

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:

One way ..

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

Copy down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"nfbelo" wrote in message
...
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

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"nfbelo" wrote in message
...
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:

One way ..

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

Copy down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"nfbelo" wrote in message
...
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

"nfbelo" wrote
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 ;

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 !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----