Sumif of multiple Index matches against one value

user_13974161 0
Viewed 134

Need help regarding Excel dynamically search based sum of two columns matching from two different tables.

I have got this Table of Data Entered One Time

    A          B        C  
1   Qlty       Warp     Weft
2   Stpl.1     150      20
3   Cotn.1     80       60
4   Stpl.2     20       20
5   Cotn.2     20       20
6   Stpl.3     20       40

in Column A2:A6, Quality can not be duplicated, its a unique Name

The Data entry and report Table is here

       A            B              C            D               E               F
8      Yarn Name    Sent Bags      Remaining    Qualty          Used Warp       Used Weft
9      20           800            600          Stpl.1          71              200
10     150          101            30           Stpl.2          70              30
11     40           300            290          Stpl.3          100             10
12     20                          400

C9:C5000 is Returning Column, Values are calculated on the base of Column A9:A5000 (Yarn Name) Need to Find Yarn Name (eg:) "20" in B2:B6 AND/OR C2:C6, wherever it matches, index that Quality from A2:A6 Then match the returned qualities(could be more than one) to D9:D5000 and sum the mathced results from E9:F5000

I have tried so far in C12

=SUMIF($A$9:$A12,A12,$B$9:$B12)-(SUMIF($D$9:$D12,INDEX($A$2:$A$6,MATCH(A12,$B$2:$B$6,0)),$D$9:$D12)+SUMIF($D$9:$D12,INDEX($A$2:$A$6,MATCH(A12,$C$2:$C$6,0)),$D$9:$D12))

PS:- I am using Excel 2007

excel-2010 excel-2007 excel-formula excel
1 Answers

If I understand correctly, then following array formula can help you:

=SUM(IFERROR(INDEX($A$2:$A$6,N(IF(1,(MMULT(--($B$2:$C$6=A9),TRANSPOSE(COLUMN($B$2:$C$6)^0))>0)*(ROW($B$2:$C$6))-1)))=TRANSPOSE($D$9:$D$12),0)*TRANSPOSE($E$9:$E$12+$F$9:$F$12))

Array formula after editing is confirmed by pressing ctrl + shift + enter

enter image description here

Edit:

To calculate Warp and Weft columns separately use following array formula:

=SUM(IFERROR(INDEX($A$2:$A$6,N(IF(1,((A9=$B$2:$B$6)*ROW($B$2:$B$6))-1)))=TRANSPOSE($D$9:$D$12),0)*TRANSPOSE($E$9:$E$12))+SUM(IFERROR(INDEX($A$2:$A$6,N(IF(1,((A9=$C$2:$C$6)*ROW($C$2:$C$6))-1)))=TRANSPOSE($D$9:$D$12),0)*TRANSPOSE($F$9:$F$12))
user_121888610
Related