Filtering in Excel with Cumulative Value

[akpflow]

[TABLE="width: 500"]
<tbody></tbody>[/TABLE]
[TABLE="width: 660"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Player (Filter)
[/TD]
[TD]Competition (Filter)
[/TD]
[TD]Venue (Filter)
[/TD]
[TD]Bonus
[/TD]
[TD]Gross Bonus[/TD]
[/TR]
[TR]
[TD]Vardy[/TD]
[TD]EPL[/TD]
[TD]Home[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Huth[/TD]
[TD]FA cup[/TD]
[TD]Away[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]Fuchs[/TD]
[TD]Carling Cup[/TD]
[TD]Home[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]105[/TD]
[/TR]
[TR]
[TD]Gray[/TD]
[TD]Champion League[/TD]
[TD]Home[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]175
[/TD]
[/TR]
[TR]
[TD]Vardy[/TD]
[TD]Champion League[/TD]
[TD]Away[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]275[/TD]
[/TR]
[TR]
[TD]Gray[/TD]
[TD]Carling Cup[/TD]
[TD]Home[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]Huth[/TD]
[TD]EPL[/TD]
[TD]Home[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]350[/TD]
[/TR]
[TR]
[TD]Vardy[/TD]
[TD]Carling Cup[/TD]
[TD]Home[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]375
[/TD]
[/TR]
</tbody>[/TABLE]


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.
[TABLE="width: 660"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Player[/TD]
[TD]Competition[/TD]
[TD]Venue[/TD]
[TD]Bonus[/TD]
[TD]Gross Bonus[/TD]
[/TR]
[TR]
[TD]Vardy[/TD]
[TD]EPL[/TD]
[TD]Home[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Vardy[/TD]
[TD]Champion League[/TD]
[TD]Away[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]275[/TD]
[/TR]
[TR]
[TD]Vardy[/TD]
[TD]Carling Cup[/TD]
[TD]Home[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]375[/TD]
[/TR]
</tbody>[/TABLE]
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
Player (Filter)	Competition (Filter)	Venue (Filter)	Bonus	Gross Bonus	Gross only visible
Vardy	EPL	Home
Huth	FA cup	Away
Fuchs	Carling Cup	Home
Gray	Champion League	Home
Vardy	Champion League	Away
Gray	Carling Cup	Home
Huth	EPL	Home
Vardy	Carling Cup	Home

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]80[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]105[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]70[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]175[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]275[/TD]
[TD="align: right"]275[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]350[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]375[/TD]
[TD="align: right"]375[/TD]

</tbody>

Feuil1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=AGGREGATE(9,5,$D$1:D2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




When filtered :

Excel 2010 32 bit

	A	B	C	D	E	F
Player (Filter)	Competition (Filter)	Venue (Filter)	Bonus	Gross Bonus	Gross only visible
Vardy	EPL	Home
Huth	EPL	Home

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]100[/TD]

</tbody>

Feuil1​