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

 

[alansidman]

if this presentation works for you then Power Query

Book5
	F	G	H	I
1	column A	column B	2	Count
2	111516	503	2	4
3	30418	501	3	5
4	56418	313	4	6
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"column A", "column B", "2"}, {{"Count", each List.Sum([no. of items in column B]), type number}})
in
    #"Grouped Rows"

 

[Jesan]

Thanks, How about a non-power query formulas?

 

[Kaper]

Looking at the sample SUMIF shall be enough:

=SUMIF(A$2:A$10,A2,C$2:C$10)

Book1
	A	B	C	D	E	F
1	column A	column B	no. of items in column B	=COUNTIF(A2:A10,A2)	need result - sum of items in column C	formula
2	111516	503	2	2	4	4
3	111516	503	2	2	4	4
4	30418	501	1	3	5	5
5	30418	501	2	3	5	5
6	30418	501	2	3	5	5
7	56418	313	1	4	6	6
8	56418	313	1	4	6	6
9	56418	313	2	4	6	6
10	56418	313	2	4	6	6
Sheet5

Cell Formulas
Range	Formula
F2:F10	F2	=SUMIF(A$2:A$10,A2,C$2:C$10)

 

[Jesan]

This is perfect. Thank you. How about this one?

column A	column B	column C	column D	need result
111516	503	ballpeen	ballpeen	With (1) paper
111516	503	ballpeen	ballpeen	With (1) paper
111516	503	ballpeen	paper	With (1) paper
30418	501	eraser	eraser	With (1) ballpeen and (2) pencil
30418	501	eraser	ballpeen	With (1) ballpeen and (2) pencil
30418	501	eraser	pencil	With (1) ballpeen and (2) pencil
30418	501	eraser	pencil	With (1) ballpeen and (2) pencil
56418	313	pencil	ballpeen	With (1) ballpeen, (1) board, (1) chalk, (1) eraser and (1) pentel pen
56418	313	pencil	eraser	With (1) ballpeen, (1) board, (1) chalk, (1) eraser and (1) pentel pen
56418	313	pencil	board	With (1) ballpeen, (1) board, (1) chalk, (1) eraser and (1) pentel pen
56418	313	pencil	chalk	With (1) ballpeen, (1) board, (1) chalk, (1) eraser and (1) pentel pen
56418	313	pencil	pentel pen	With (1) ballpeen, (1) board, (1) chalk, (1) eraser and (1) pentel pen

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

What is the logic for the desired results in post #5?

 

[Kaper]

I think it could go for counting items in column D which are different than in column C.
Having headers with names in row 1 it could be done (0 can be formattaed as non-displaying using custom format of the cell).

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	column A	column B	column C	column D	ballpeen	board	chalk	eraser	paper	pencil	pentel pen	need result
2	111516	503	ballpeen	ballpeen	0	0	0	0	1	0	0	With (1) paper
3	111516	503	ballpeen	ballpeen	0	0	0	0	1	0	0	With (1) paper
4	111516	503	ballpeen	paper	0	0	0	0	1	0	0	With (1) paper
5	30418	501	eraser	eraser	1	0	0	0	0	2	0	With (1) ballpeen and (2) pencil
6	30418	501	eraser	ballpeen	1	0	0	0	0	2	0	With (1) ballpeen and (2) pencil
7	30418	501	eraser	pencil	1	0	0	0	0	2	0	With (1) ballpeen and (2) pencil
8	30418	501	eraser	pencil	1	0	0	0	0	2	0	With (1) ballpeen and (2) pencil
9	56418	313	pencil	ballpeen	1	1	1	1	0	0	1	With (1) ballpeen, (1) board, (1) chalk, (1) eraser and (1) pentel pen
10	56418	313	pencil	eraser	1	1	1	1	0	0	1	With (1) ballpeen, (1) board, (1) chalk, (1) eraser and (1) pentel pen
11	56418	313	pencil	board	1	1	1	1	0	0	1	With (1) ballpeen, (1) board, (1) chalk, (1) eraser and (1) pentel pen
12	56418	313	pencil	chalk	1	1	1	1	0	0	1	With (1) ballpeen, (1) board, (1) chalk, (1) eraser and (1) pentel pen
13	56418	313	pencil	pentel pen	1	1	1	1	0	0	1	With (1) ballpeen, (1) board, (1) chalk, (1) eraser and (1) pentel pen
Sheet1

Cell Formulas
Range	Formula
E2:K13	E2	=SUMPRODUCT(($A$2:$A$13=$A2)*($C$2:$C$13<>$D$2:$D$13)*($D$2:$D$13=E$1))

 

[Jesan]

Hi Kaper, Thank you for this. I would like to ask about the formula you use in column L?

 

[Peter_SSs]

I would like to ask about ..

What about the question I asked?

What is the logic for the desired results in post #5?

Also, you did not act on my other suggestion. Therefore we still do not know what excel version(s) you are using so we do not know what functions and other features you have available.

Always best to make it as easy as possible for people to help you.

 

[Jesan]

Hi Peter, sorry about the that, the excel i have is the 2021 version 2501, about the logic, i need to skip some steps to make it more faster.