Sum with sumifs-non array formula

[SAXON10]

Hi,

Column A,B,C,D and E contain Code, category, Type 1, Type 2 and Total stock. I am trying to achieve sumifs according to the material type and

category. I am apply 5 different array formulas for the following data. I need one non array formula for all together.

Any help much appreciated

Thanks in advance.

Data.

Category-----X

Type---------209,30B,1VO,1LP,GBR,50R

Typr---------TTR-LER,PPT-JKW,PATTLE

MI1 contains category and types----30B, 30R,40R,50R,1VO

MI2 contains category and types----1LP and TTR-LER

MI3 contains category and types----1LP and PPT-JKW

MI4 contains category and types----GBR,209,30B, 30R,40R,50R,1VO,1LP and X

MI5 contains category and types----GBR,209,PATTLE

MI1-------B2=SUM(SUMIFS(DATA!$E:$E,DATA!$A:$A,$A2,DATA!$C:$C,$H$2:$H$9,DATA!$B:$B,"<>X"))

MI2-------C2=SUM(SUMIFS(DATA!$E:$E,DATA!$A:$A,$A2,DATA!$C:$C,$J$2:$J$9,DATA!$D:$D,$K$2:$K$9))

MI3-------D2=SUM(SUMIFS(DATA!$E:$E,DATA!$A:$A,$A2,DATA!$C:$C,$L$2:$L$9,DATA!$D:$D,$M$2:$M$9))

MI4-------E2=SUM(SUMIFS(DATA!$E:$E,DATA!$E:$E,">0",DATA!$A:$A,$A2,DATA!$C:$C,$N$2:$N$9,DATA!$B:$B,$O$2))

MI5-------F2=SUM(SUMIFS(DATA!$E:$E,DATA!$A:$A,$A2,DATA!$C:$C,$P$2:$P$9,DATA!$B:$B,"<>X"))


https://www.dropbox.com/s/r7zbxptx6aptxkr/SUM WITH SUMIFS.XLSX?dl=0

https://www.dropbox.com/s/edj3hinhwy5m8si/MI1.PNG?dl=0

https://www.dropbox.com/s/ow2ufbu5rklfhu8/MI2.PNG?dl=0

 

[SAXON10]

Please help me anyone.

 

[SAXON10]

Please advise something because I have spend lot time calculate the sum of qty with different ranges.