possible formula

[Jesan]

Hi,

Thank you in advance. Is there a way to know some single excel formula for this table?

column A	column B	no. of items in column B	=COUNTIF(A2:A10,A2)	need result - sum of items in column C
111516	503	2	2	4
111516	503	2	2	4
030418	501	1	3	5
030418	501	2	3	5
030418	501	2	3	5
056418	313	1	4	6
056418	313	1	4	6
056418	313	2	4	6
056418	313	2	4	6

 

[Cubist]

I don't know which version you have but an option in 365:

Book1
	A	B	C	D	E
1	column A	column B	column C	column D	Formula
2	111516	503	ballpeen	ballpeen	With (1) paper
3	111516	503	ballpeen	ballpeen	With (1) paper
4	111516	503	ballpeen	paper	With (1) paper
5	30418	501	eraser	eraser	With (1) ballpeen, and (2) pencil
6	30418	501	eraser	ballpeen	With (1) ballpeen, and (2) pencil
7	30418	501	eraser	pencil	With (1) ballpeen, and (2) pencil
8	30418	501	eraser	pencil	With (1) ballpeen, and (2) pencil
9	56418	313	pencil	ballpeen	With (1) ballpeen, (1) board, (1) chalk, (1) eraser, and (1) pentel pen
10	56418	313	pencil	eraser	With (1) ballpeen, (1) board, (1) chalk, (1) eraser, and (1) pentel pen
11	56418	313	pencil	board	With (1) ballpeen, (1) board, (1) chalk, (1) eraser, and (1) pentel pen
12	56418	313	pencil	chalk	With (1) ballpeen, (1) board, (1) chalk, (1) eraser, and (1) pentel pen
13	56418	313	pencil	pentel pen	With (1) ballpeen, (1) board, (1) chalk, (1) eraser, and (1) pentel pen
Sheet2

Cell Formulas
Range	Formula
E2:E13	E2	=LET( g,CHOOSECOLS(GROUPBY($D$2:$D$13,$D$2:$D$13,LAMBDA(x,"("&COUNTA(x)&")"),,0,,($A$2:$A$13=A2)*($C$2:$C$13<>$D$2:$D$13)),2,1), "With " & IFERROR(TEXTJOIN({" ",", "},,DROP(g,-1)) & ", and ","") & TEXTJOIN({" ",", "},,TAKE(g,-1)) )