Filtering in Excel with Cumulative Value

[akpflow]

<tbody></tbody>

Player (Filter)	Competition (Filter)	Venue (Filter)	Bonus	Gross Bonus
Vardy	EPL	Home	50	50
Huth	FA cup	Away	30	80
Fuchs	Carling Cup	Home	25	105
Gray	Champion League	Home	70	175
Vardy	Champion League	Away	100	275
Gray	Carling Cup	Home	25	300
Huth	EPL	Home	50	350
Vardy	Carling Cup	Home	25	375

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

The above table shows players name, competition, venue, bonus and gross bonus. The gross bonus is the cumulative sum of the bonus. If I make a selection using a filter (Player name), The gross bonus doesn't respond to the filter selection.

Player	Competition	Venue	Bonus	Gross Bonus
Vardy	EPL	Home	50	50
Vardy	Champion League	Away	100	275
Vardy	Carling Cup	Home	25	375

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

How can I fix this challenge?
Please I need your help. Thanks in advance...

 

[dchaney]

So, in your example above you indicate that the Gross Bonus is cumulative, however the example gross bonus does not equal to the increasing amount of the bonus... Are you saying for example that Vary's 3 Gross Bonuses should equal what you have there, or you need them to equal 50, then 150, then 175?

 

[dchaney]

If it is the latter above, then try this formula... assuming your data is in Columns A - D and the gross bonus is in E paste this in Cell E2 and drag down

=SUMIF(A$2:$A2,A2,D$2:D2)

 

[akpflow]

If it is the latter above, then try this formula... assuming your data is in Columns A - D and the gross bonus is in E paste this in Cell E2 and drag down

=SUMIF(A$2:$A2,A2,D$2:D2)

@dchaney.. thank you for your response
But it didn't give me the desired result,
The first table is showing the original state. The columns (players, Competition, Venue) are all filter that can help me narrow my search and see key information.
For example, I want to see Huth bonus.... Huth's bonus (only Huth is selected at the player's filter) is 30+50 = 80.
Someone might want to see his bonus at EPL competition only (only EPL is selected at the competition's filter) is 50 and so on...
I guess you understand now.. or better still I can I share a demo file of xl doc here?

 

[netuser]

Here you go, it was AGGREGATE function that you need for this Put formula in F2 and drag it below then test it by using filter to compare with G

Excel 2010 32 bit
	A	B	C	D	E	F
1	Player (Filter)	Competition (Filter)	Venue (Filter)	Bonus	Gross Bonus	Gross only visible
2	Vardy	EPL	Home	50	50	50
3	Huth	FA cup	Away	30	80	80
4	Fuchs	Carling Cup	Home	25	105	105
5	Gray	Champion League	Home	70	175	175
6	Vardy	Champion League	Away	100	275	275
7	Gray	Carling Cup	Home	25	300	300
8	Huth	EPL	Home	50	350	350
9	Vardy	Carling Cup	Home	25	375	375
Feuil1

Cell Formulas
Range	Formula
F2	=AGGREGATE(9,5,$D$1:D2)

When filtered :

Excel 2010 32 bit
	A	B	C	D	E	F
1	Player (Filter)	Competition (Filter)	Venue (Filter)	Bonus	Gross Bonus	Gross only visible
2	Vardy	EPL	Home	50	50	50
8	Huth	EPL	Home	50	350	100
Feuil1

 

[akpflow]

Hello sir, It worked... Thank you for your help....

Here you go, it was AGGREGATE function that you need for this Put formula in F2 and drag it below then test it by using filter to compare with G


Excel 2010 32 bit

	A	B	C	D	E	F
1	Player (Filter)	Competition (Filter)	Venue (Filter)	Bonus	Gross Bonus	Gross only visible
2	Vardy	EPL	Home	50	50	50
3	Huth	FA cup	Away	30	80	80
4	Fuchs	Carling Cup	Home	25	105	105
5	Gray	Champion League	Home	70	175	175
6	Vardy	Champion League	Away	100	275	275
7	Gray	Carling Cup	Home	25	300	300
8	Huth	EPL	Home	50	350	350
9	Vardy	Carling Cup	Home	25	375	375

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Feuil1​

Worksheet Formulas Cell Formula F2 =AGGREGATE(9,5,$D$1:D2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

When filtered :

Excel 2010 32 bit

	A	B	C	D	E	F
1	Player (Filter)	Competition (Filter)	Venue (Filter)	Bonus	Gross Bonus	Gross only visible
2	Vardy	EPL	Home	50	50	50
8	Huth	EPL	Home	50	350	100

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Feuil1​