# Sum with sumifs-non array formula

#### SAXON10

##### Board Regular
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

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

### Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### SAXON10

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

Replies
0
Views
65
Replies
1
Views
77
Replies
2
Views
144
Replies
20
Views
300
Replies
4
Views
56