Counting values

[Michael1727]

Hello. This forum has been very helpful in the past. Not sure what to title this as a topic, but I will explain what I'm trying to do in an example:

In the below example I have no formatting or equations, I have just set up the table how I am envisioning it might work. In column A I have an ongoing list of part numbers that are defective. the 6th digit (in red) is the shift number (1-4). My goal is to extract the shift number for each part number and populate a tally into a column for that shift number (B-E) so that the column for that shift number can keep an ongoing total (row 2) of how many part numbers appear with that shift number. I'm then planning on using the totals in row 2 as data to be expressed in a graph. I hope that makes sense.

Thank you.

 

[Sufiyan97]

Try

Book3
	A	B	C	D	E	F
1		1	2	3	4
2		1	1	2	0
3	202022027		1
4	220623239			1
5	220871048	1
6	220873040			1
7
8
Sheet2

Cell Formulas
Range	Formula
B2:E2	B2	=SUM(B3:B6)
B3:E6	B3	=IF(MID($A3,6,1)+0=B$1,1,"")

 

[Michael1727]

Try

Book3
	A	B	C	D	E	F
1		1	2	3	4
2		1	1	2	0
3	202022027		1
4	220623239			1
5	220871048	1
6	220873040			1
7
8
Sheet2

Cell Formulas
Range	Formula
B2:E2	B2	=SUM(B3:B6)
B3:E6	B3	=IF(MID($A3,6,1)+0=B$1,1,"")

I'll give this a try. Thank you. How would I set the second range for the first formula to be ongoing: =SUM (B3:B..............)?

 

[JamesCanale]

This one might be automatically expandable. I'm not sure how well it will go in 2021, but it should be okay.

MrExcelPlayground17.xlsx
	A	B	C	D	E
1		1	2	3	4
2		1	4	3	1
3	202022027	0	1	0	0
4	220623239	0	0	1	0
5	220871048	1	0	0	0
6	220873040	0	0	1	0
7	221164028	0	0	0	1
8	221682024	0	1	0	0
9	221682035	0	1	0	0
10	221882004	0	1	0	0
11	221993070	0	0	1	0
Sheet28

Cell Formulas
Range	Formula
B1:E1	B1	=TRANSPOSE((SORT(UNIQUE(MID(A3:A11,6,1)))))
B2:E2	B2	=TRANSPOSE(MMULT(TRANSPOSE(B3#),SEQUENCE(ROWS(B3#),1,1,0)))
B3:E11	B3	=IF(MID(A3:A11,6,1)=B1#,1,0)
Dynamic array formulas.

 

[Fluff]

Another option as you have xl 2021

Fluff.xlsm
	A	B	C	D	E
1		1	2	3	4
2		1	1	2	1
3	202022027		1
4	220623239			1
5	220871048	1
6	220873040			1
7	221164028				1
Home

Cell Formulas
Range	Formula
B2:E2	C2	=SUM(INDEX($B3#,,COLUMNS($B2:C2)))
B3:E7	B3	=LET(f,FILTER(A3:A100,A3:A100<>""),IF(--MID(f,6,1)=B1:E1,1,""))
Dynamic array formulas.

 

[Michael1727]

You'll have to excuse my ignorance. I was able to populate row 2 with the formulas =SUM(B3:B6) but I'm not sure how or where to populate the formula for the remaining rows and columns. Whenever I do, row 2 shows "#SPILL!"

 

[Fluff]

For the formula I suggested you need to clear Columns B:E from row 3 down & then put the formula in B3 only.

 

[Michael1727]

For the formula I suggested you need to clear Columns B:E from row 3 down & then put the formula in B3 only.

Aha. Got it tow work. Wow, the formula even recognized what the 6th digit is and populates it in the correct column. I would never have figured this out. Thank you

 

[Fluff]

Glad we could help & thanks for the feedback.