SUMPRODUCT or SUMIF
 

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
Can you help with this??

This would be my formula for cell H3

=SUMPRODUCT(A3:D3,$A$1:$D$1,--($A$2:$D$2=$H$2))


"nfbelo" wrote:

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
Can you help with this??

Here's a 2nd response to your earlier identical post ?? .. :
------------
"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
----