Sum with sumifs-non array formula

SAXON10

Board Regular
Joined
Jun 1, 2017
Messages
107
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
 

Some videos you may like

Excel Facts

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

SAXON10

Board Regular
Joined
Jun 1, 2017
Messages
107
Please advise something because I have spend lot time calculate the sum of qty with different ranges.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,311
Messages
5,527,947
Members
409,794
Latest member
ajithppajith

This Week's Hot Topics

Top