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 <[email protected]>, [email protected] 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 <[email protected]>, [email protected] 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 <[email protected]>, [email protected] 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.
>>>>
>>>>.
>>>>
>>
>>