count if and sum if on filtered cells (dynamic)

[nikos1984]

Hello everyone,​

I' m trying to create a dynamic table which will sum the visible rows (C) based on common values (A) and show the result at the end of each repeated value.
More specifically I' m using this specific formula (found online) IF(COUNTIF($A$2:A2;A2)=COUNTIF($A$2:$A$14;A2);SUMIF($A$2:$A$74;A2;$C$2:$C$14);" ") but it doesn't work when I apply filters for specific processes (B).
The repeated values (A) are already filtered and sorted by using the FILTER formula from a helper table.
The basic form of the table is as shown below

	Process	Tool	Cost	Total cost per process
1	2006SE06900001	DKA	4.209,34 €​	4.209,34 €​
2	2007SE26900000	DKA	3.701,63 €​
3	2007SE26900000	DKA	27.878,90 €​	31.580,53 €​
4	2008SE06900017	DKA	4.989,90 €​
5	2008SE06900017	DKA	36.538,38 €​
6	2008SE06900017	DKA	79.072,80 €​	120.601,08 €​
7	2010SE06900002	DKA	9.606,54 €​	9.606,54 €​
8	2013SE06900000	DKA	2.993,31 €​
9	2013SE06900000	DKA	20.479,48 €​
10	2013SE06900000	EKTEDR	72,38 €​	23.545,17 €​
11	2014SE06900001	DKA	40.340,97 €​
12	2014SE06900001	DKA	65.222,32 €​
13	2014SE06900001	EPEN	16.020,00 €​	121.583,29 €​

When I apply the filter it shows like below

	Process	Tool	Cost	Total cost per process
1	2006SE06900001	DKA	4.209,34 €​	4.209,34 €​
2	2007SE26900000	DKA	3.701,63 €​
3	2007SE26900000	DKA	27.878,90 €​	31.580,53 €​
4	2008SE06900017	DKA	4.989,90 €​
5	2008SE06900017	DKA	36.538,38 €​
6	2008SE06900017	DKA	79.072,80 €​	120.601,08 €​
7	2010SE06900002	DKA	9.606,54 €​	9.606,54 €​
8	2013SE06900000	DKA	2.993,31 €​
9	2013SE06900000	DKA	20.479,48 €​	?????​
11	2014SE06900001	DKA	40.340,97 €​
12	2014SE06900001	DKA	65.222,32 €​
13	2014SE06900001	EPEN	16.020,00 €​	121.583,29 €​

As you can see the 10th row disapeared but it doesn't apply the sum from the 8th and 9th rows.
The result I want is as show below

	Process	Tool	Cost	Total cost per process
1	2006SE06900001	DKA	4.209,34 €​	4.209,34 €​
2	2007SE26900000	DKA	3.701,63 €​
3	2007SE26900000	DKA	27.878,90 €​	31.580,53 €​
4	2008SE06900017	DKA	4.989,90 €​
5	2008SE06900017	DKA	36.538,38 €​
6	2008SE06900017	DKA	79.072,80 €​	120.601,08 €​
7	2010SE06900002	DKA	9.606,54 €​	9.606,54 €​
8	2013SE06900000	DKA	2.993,31 €​
9	2013SE06900000	DKA	20.479,48 €​	23.472,79 €​
11	2014SE06900001	DKA	40.340,97 €​
12	2014SE06900001	DKA	65.222,32 €​
13	2014SE06900001	EPEN	16.020,00 €​	121.583,29 €​

I' ve tried the SUMPRODUCT-SUBTOTAL-OFFSET solution but it doesn't seem to work
Any sugestion will be much appreciated

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1	Process	Tool	Cost		Total cost per process
2	2006SE06900001	DKA	4209.34	1	4209.34
3	2007SE26900000	DKA	3701.63	1
4	2007SE26900000	DKA	27878.9	1	31580.53
5	2008SE06900017	DKA	4989.9	1
6	2008SE06900017	DKA	36538.38	1
7	2008SE06900017	DKA	79072.8	1	120601.08
8	2010SE06900002	DKA	9606.54	1	9606.54
9	2013SE06900000	DKA	2993.31	1
10	2013SE06900000	DKA	20479.48	1	23472.79
12	2014SE06900001	DKA	40340.97	1
13	2014SE06900001	DKA	65222.32	1
14	2014SE06900001	EPEN	16020	1	121583.29
Main

Cell Formulas
Range	Formula
D2:D10,D12:D14	D2	=SUBTOTAL(103,A2)
E2:E10,E12:E14	E2	=IF(COUNTIFS(A$2:A2,A2,D$2:D2,1)=COUNTIFS(A:A,A2,D:D,1),SUMIFS(C:C,A:A,A2,D:D,1),"")

 

[nikos1984]

Works like a charm!
Thank you very much
Very elegant way to nest subtotal

 

[Fluff]

You're welcome & thanks for the feedback.