Count multiples rows and ans in a single row and column

[AFZAL SOHAIL]

Dear Team,

I am a sharing a data in which the count of product are in multiple rows , but I want to ans in a single row and column is as under:-

Sr.No.	Product	Count	Results	Expected results
1	Apples	2	Apples	2	Apples	2
6	Apples	2	Banana	3
5	Banana	3	Banana	3	Grapes	2
9	Banana	3	Mangoes	2
11	Banana	3
3	Grapes	2	Grapes	2
4	Grapes	2
2	Mangoes	2	Mangoes	2
10	Mangoes	2

 

[AFZAL SOHAIL]

In that case perhaps you need to update your forum profile? Your profile shows that you have Excel versions 2021 and 2016, but neither of those versions has the HSTACK function.

You are right sir, I use in my office office 365, but mostly and in home i use office -2016,2021.
Kindly share a formula for these old version too.
Thanks in advance

 

[Sergius]

Hello! This should help.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Sr.No.	Product	Count	Results	Expected results
2	1	Apples	2	Apples	2	Apples	2			Apples	2
3	6	Apples	2	Banana	3					Banana	3
4	5	Banana	3	Banana	3	Grapes	2			Grapes	2
5	9	Banana	3	Mangoes	2					Mangoes	2
6	11	Banana	3
7	3	Grapes	2	Grapes	2
8	4	Grapes	2
9	2	Mangoes	2	Mangoes	2
10	10	Mangoes	2
Sheet3

Cell Formulas
Range	Formula
J2:J5	J2	=UNIQUE(B2:B10)
K2:K5	K2	=COUNTIFS(B2:C10,J2)
Dynamic array formulas.

 

[AFZAL SOHAIL]

@Sergius Thanks a lot Sir,
it is working very well.
May God bless you.

 

[Peter_SSs]

You are right sir, I use in my office office 365, but mostly and in home i use office -2016,2021.

Then perhaps you should still update your profile to include 365 as well since you do use it at work. Then when you start a thread, make it clear which version or versions an answer needs to work in.