Need help in Filter

[Santosh080]

Hello Friends
Please help me so that i can do my job quickly in my office. I have an excel file with data like below.
A B C D E

Type	Name	Phone	Position	Cost
111	AAA	9214	R	100
111	ALD	4532	R	250
111	NVK	4271	F	300
222	LFO	6534	F	500
222	PGV	2872	R	100
222	FOR	7575	R	600
333	FLP	2727	F	750
333	BVH	2278	F	100
333	DGY	8372	R	900

<tbody>
</tbody>

Now i need the above data in the following format,

I want to put "Type" value like 111 or 222 etc. in a cell and the in below "Position" table all data dragged from above table. Can it possible? please help. I do not have any VBA knowledge but i can use formulas like vlookup.

Type 111 <tbody> </tbody> Position R I need here all the number of R from that selected "Type" I need here the total cost of R from that selected "Type" I need here the number of R whose cost is above 150 from that selected "Type" I need here the total cost of R whose cost is above 150 from that selected "Type" F I need here all the number of F from that selected "Type" I need here the total cost of F from that selected "Type" I need here the number of F whose cost is above 150 from that selected "Type" I need here the total cost of F whose cost is above 150 from that selected "Type" <tbody> </tbody>

<tbody>
</tbody>

 

[AlanY]

there you are

Book1
	A	B	C	D	E
1	Type	Name	Phone	Position	Cost
2	111	AAA	9214	R	100
3	111	ALD	4532	R	250
4	111	NVK	4271	F	300
5	222	LFO	6534	F	500
6	222	PGV	2872	R	100
7	222	FOR	7575	R	600
8	333	FLP	2727	F	750
9	333	BVH	2278	F	100
10	333	DGY	8372	R	900
11
12	Type
13	111
14
15
16	Position	No	Total	>150	<=150
17	R	2	350	250	100
18	F	1	300	300	0
Sheet1

Cell Formulas
Range	Formula
B17	=COUNTIFS($A$2:$A$10,$A$13,$D$2:$D$10,$A17)
C17	=SUMIFS($E$2:$E$10,$A$2:$A$10,$A$13,$D$2:$D$10,$A17)
D17	=SUMIFS($E$2:$E$10,$A$2:$A$10,$A$13,$D$2:$D$10,$A17,$E$2:$E$10,">150")
E17	=C17-D17

 

[Santosh080]

Thank you very much for help.

In D17 i want the number of R which have above cost above 150 and in E17 cell i need sum of that value.

means if R have two value above 150 ( for example 200 and 300 ) then in cell D17 it should show 2 and in E17 it should show sum of 200 and 300 i.e 500. Can it possible?
i think you understand what i want to tell.

 

[sandy666]

maybe use PivotTable from QueryTable

Type	Name	Phone	Position	Cost		Type	111
111​	AAA	9214​	R	100​
111​	ALD	4532​	R	250​		Position	Count of Cost	Sum of Cost	Count of Cost>150	Sum of Cost>150
111​	NVK	4271​	F	300​		F	1​	300​	1​	300​
222​	LFO	6534​	F	500​		R	2​	350​	1​	250​
222​	PGV	2872​	R	100​		Grand Total	3​	650​	2​	550​
222​	FOR	7575​	R	600​
333​	FLP	2727​	F	750​
333​	BVH	2278​	F	100​
333​	DGY	8372​	R	900​

With PowerQuery

QueryTable
Type	Name	Phone	Position	Cost	Cost>150
111​	AAA	9214​	R	100​
111​	ALD	4532​	R	250​	250​
111​	NVK	4271​	F	300​	300​
222​	LFO	6534​	F	500​	500​
222​	PGV	2872​	R	100​
222​	FOR	7575​	R	600​	600​
333​	FLP	2727​	F	750​	750​
333​	BVH	2278​	F	100​
333​	DGY	8372​	R	900​	900​

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Conditional Column" = Table.AddColumn(Source, "Cost>150", each if [Cost] > 150 then [Cost] else null)
in
    #"Added Conditional Column"[/SIZE]

load As connection then insert PivotTable with Use an external connection

 

[AlanY]

Thank you very much for help.

In D17 i want the number of R which have above cost above 150 and in E17 cell i need sum of that value.

means if R have two value above 150 ( for example 200 and 300 ) then in cell D17 it should show 2 and in E17 it should show sum of 200 and 300 i.e 500. Can it possible?
i think you understand what i want to tell.

Book1
	A	B	C	D	E
1	Type	Name	Phone	Position	Cost
2	111	AAA	9214	R	100
3	111	ALD	4532	R	250
4	111	NVK	4271	F	300
5	222	LFO	6534	F	500
6	222	PGV	2872	R	100
7	222	FOR	7575	R	600
8	333	FLP	2727	F	750
9	333	BVH	2278	F	100
10	333	DGY	8372	R	900
11
12	Type
13	111
14
15
16	Position	No	Total	>150	<=150
17	R	2	350	1	250
18	F	1	300	300	300
Sheet1

Cell Formulas
Range	Formula
D17	=COUNTIFS($A$2:$A$10,$A$13,$D$2:$D$10,$A17,E2:E10,">150")
E17	=SUMIFS($E$2:$E$10,$A$2:$A$10,$A$13,$D$2:$D$10,$A17,E2:E10,">150")