| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
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. .. |
|
#2
|
|||
|
|||
|
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. . |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
=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. . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
