Array look and Sum
 

Hi,

I have 350 codes in column H and I want to look down A1:A30 and
see if any non blank values there matche any in Column H then add
the relevant cells in other columns like B, C, D etc

Any help will be greatly appreciated.



Fais.

..

The query is a bit underspecified...

1]

=SUMIF($A$2:$A$30,H2,$B$2:$B$30)

2]

=SUMPRODUCT(--($A$2:$A$30=H2),$B$2:$B$30+$C$2:$C$30+$D$2:$D$30)

might point you in the right direction.

Fais wrote:
Hi,

I have 350 codes in column H and I want to look down A1:A30 and
see if any non blank values there matche any in Column H then add
the relevant cells in other columns like B, C, D etc

Any help will be greatly appreciated.



Fais.

.

sorry if not clear.

My data might will look like this :

A B C H
Col Col Col Col

1 4 5 33

77 3 10 66

2 20 22 101

66 40 80 47

33 10 15 887

1110
2245

=SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$6,$H$2:$H$6,0)),B$2:B$6)

=SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$6,$H$2:$H$6,0)),C$2:C$6)

Fais wrote:

sorry if not clear.

My data might will look like this :

A B C H
Col Col Col Col

1 4 5 33

77 3 10 66

2 20 22 101

66 40 80 47

33 10 15 887

1110
2245
.

.
I need to add B then add C if the column A value is listed

in th H column. In my exmaple I add the values in the 4th row
and the last row because 66 & 33 do exist in the H column

so the subtotals for B & C will be :

B C

50 95

Thanks



In article , says...


The query is a bit underspecified...

1]

=SUMIF($A$2:$A$30,H2,$B$2:$B$30)

2]

=SUMPRODUCT(--($A$2:$A$30=H2),$B$2:$B$30+$C$2:$C$30+$D$2:$D$30)

might point you in the right direction.

Fais wrote:

Hi,

I have 350 codes in column H and I want to look down A1:A30 and
see if any non blank values there matche any in Column H then add
the relevant cells in other columns like B, C, D etc

Any help will be greatly appreciated.



Fais.

.

works perfect.

Many Thanks

Fais
+++++++++++++++++++++++++++++++++++


In article , says...


=SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$6,$H$2:$H$6,0)),B$2:B$6)

=SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$6,$H$2:$H$6,0)),C$2:C$6)

Fais wrote:

sorry if not clear.

My data might will look like this :

A B C H
Col Col Col Col

1 4 5 33

77 3 10 66

2 20 22 101

66 40 80 47

33 10 15 887

1110
2245
.

.
I need to add B then add C if the column A value is listed

in th H column. In my exmaple I add the values in the 4th row
and the last row because 66 & 33 do exist in the H column

so the subtotals for B & C will be :

B C

50 95

Thanks



In article , says...


The query is a bit underspecified...

1]

=SUMIF($A$2:$A$30,H2,$B$2:$B$30)

2]

=SUMPRODUCT(--($A$2:$A$30=H2),$B$2:$B$30+$C$2:$C$30+$D$2:$D$30)

might point you in the right direction.

Fais wrote:

Hi,

I have 350 codes in column H and I want to look down A1:A30 and
see if any non blank values there matche any in Column H then add
the relevant cells in other columns like B, C, D etc

Any help will be greatly appreciated.



Fais.

.