Conditional "Running Sum" Ranking

[CF64]

Hi, I've looked through some conditional ranking threads but haven't found something which solves my inquiry but apologies if I may have missed a solution.
What I'm trying to do is to do something like a running rank based on point totals for a team and the cumulative weekly point totals.


For example
in column D, I create a running total of points for each team as the week's progress
in column E, I rank that cumulative total for all weeks
in column F, I would like to rank the total just up to the current week.
So, in row 3, I would have the rank of the dolphins total points (which is just 1 week) against the pats and bills for week 1,
then in row 7, would have the rank of the Dolphins total point total for week 1 and 2 against the pats and bills total points for week 1 and 2
then in row 9, would have the rank of the dolphins total points total for week 1, 2, and 3, against the pats and bills total points for weeks 1,2, and 3.

	a	b	c	d	e	f
1	Week	Team	Weekly Points	Cumulative points for team	Cumulative points rank	Running Rank using cumulative points and week
				=sumifs(c:c,b:b,b2,a:a,"<=",a2)	=rank(d2,$d$2:$d$10,0)	?
2	1	Patriots	10	10
3	1	Dolphins	5	5
4	1	Bills	10	10
5	2	Patriots	5	15
6	2	Bills	10	20
7	2	Dolphins	5	10
8	3	Patriots	5	20
9	3	Dolphins	10	20
10	3	Bills	10	30

I hope my example makes sense. Happy to provide clarification if not and thank you in advance for your consideration.

 

[CF64]

I am all set with this post. Thank you

 

[ajitexcel]

Can you please share what is the desired result in queried column.

 

[ajitexcel]

If you are looking for the following result in red

Week	Team	Weekly Points	Cumulative points for the team	Cumulative points rank	Running Rank using cumulative points and week
1	Patriots	10	10	6	1
1	Dolphins	5	5	9	3
1	Bills	10	10	6	1
2	Patriots	5	15	5	2
2	Bills	10	20	2	1
2	Dolphins	5	10	6	3
3	Patriots	5	20	2	2
3	Dolphins	10	20	2	2
3	Bills	10	30	1	1

The formula in F2 and onwards is =SUMPRODUCT(--(A2=$A$2:$A$10),--(D2<$D$2:$D$10))+1

Hope it helps

 

[CF64]

If you are looking for the following result in red

Week	Team	Weekly Points	Cumulative points for the team	Cumulative points rank	Running Rank using cumulative points and week
1	Patriots	10	10	6	1
1	Dolphins	5	5	9	3
1	Bills	10	10	6	1
2	Patriots	5	15	5	2
2	Bills	10	20	2	1
2	Dolphins	5	10	6	3
3	Patriots	5	20	2	2
3	Dolphins	10	20	2	2
3	Bills	10	30	1	1

The formula in F2 and onwards is =SUMPRODUCT(--(A2=$A$2:$A$10),--(D2<$D$2:$D$10))+1

Hope it helps

Hello and thank you for your reply. How would I add additional conditions to the same formula? For instance, if I wanted to add a column for year and a column for the "Team's" opponent?

 

[ajitexcel]

Hello there, the best way is to share data, and what is your expected result in desired columns (marked red or any different way)? it makes understanding the requirement clear.
Regards

 

[CF64]

Hello there, the best way is to share data, and what is your expected result in desired columns (marked red or any different way)? it makes understanding the requirement clear.
Regards

Hi, Apologies for the confusion with my last reply.

I'm just wondering how to add additional columns (conditions) to the formula you provided for F2 in the table above
"The formula in F2 and onwards is =SUMPRODUCT(--(A2=$A$2:$A$10),--(D2<$D$2:$D$10))+1"

So in my new table, I have a column C for Season . I would like the formula in column f to do exactly the same thing as above but take in to account the season. So the rank is based on the running weekly total for each season. I'm just not sure how add column c to the previous formula

Thank you

	a	b	c	d	e	f
	Week	Team	Season	Weekly Points	Cumulative points for the team	Running Rank using cumulative points and week
					=sumifs(d:d,c:c,c2,b:b,"<=",b2)	? how do I add column C into this formula? =SUMPRODUCT(--(A2=$A$2:$A$18),--(e2<$e$2:$e$18))+1
1	1	Patriots	2021	10	10	1
2	1	Dolphins	2021	5	5	3
3	1	Bills	2021	10	10	1
4	2	Patriots	2021	5	15	2
5	2	Bills	2021	10	20	1
6	2	Dolphins	2021	5	10	3
7	3	Patriots	2021	5	20	2
8	3	Dolphins	2021	10	20	2
9	3	Bills	2021	10	30	1
10	1	Patriots	2020	5	5	3
11	1	Dolphins	2020	10	10	2
12	1	Bills	2020	15	15	1
13	2	Patriots	2020	5	10	3
14	2	Bills	2020	10	25	1
15	2	Dolphins	2020	15	25	1
16	3	patriots	2020	5	15	3
17	3	Dolphins	2020	10	35	2
18	3	Bills	2020	15	40	1

 

[ajitexcel]

Hello there, what i make out of above is as follows

Week	Team	Season	Weekly Points	Cumulative points for the team	Running Rank using cumulative points and week	Running Rank using cumulative points, week and season
1	Patriots	2021	10	10	1	1			12021
1	Dolphins	2021	5	5	3	3			12021
1	Bills	2021	10	10	1	1			12021
2	Patriots	2021	5	15	2	2			22021
2	Bills	2021	10	20	1	1			22021
2	Dolphins	2021	5	10	3	2			22021
3	Patriots	2021	5	20	2	3			32021
3	Dolphins	2021	10	20	2	1			32021
3	Bills	2021	10	30	1	1			32021
1	Patriots	2020	5	5	3	3			12020
1	Dolphins	2020	10	10	2	2			12020
1	Bills	2020	15	15	1	1			12020
2	Patriots	2020	5	10	3	3			22020
2	Bills	2020	10	25	1	2			22020
2	Dolphins	2020	15	25	1	1			22020
3	patriots	2020	5	15	3	3			32020
3	Dolphins	2020	10	35	2	2			32020
3	Bills	2020	15	40	1	1			32020

If you are looking for the above answer, then you will need to add a helper column and then the formula would refer to the helper column instead of "A" column (=SUMPRODUCT(--(J2=$J$2:$J$19),--(D2<$D$2:$D$19))+1) here helper column is "J". Later you can hide it.
please let men know in case you are looking for a different answer.
regards

 

[ajitexcel]

Sorry for the wrong reference to the D column (=SUMPRODUCT(--(J2=$J$2:$J$19),--(D2<$D$2:$D$19))+1).
The correct reference in the formula column is (=SUMPRODUCT(--(J2=$J$2:$J$19),--(E2<$E$2:$E$19))+1) here helper column is "J".

 

[ajitexcel]

Sorry for the wrong reference to the D column (=SUMPRODUCT(--(J2=$J$2:$J$19),--(D2<$D$2:$D$19))+1).
The correct reference in the formula column is (=SUMPRODUCT(--(J2=$J$2:$J$19),--(E2<$E$2:$E$19))+1) here helper column is "J".

PLEASE IGNORE REPLY NO. 8 & 9.
Sorry for the wrong reference to the D column (=SUMPRODUCT(--(J2=$J$2:$J$19),--(D2<$D$2:$D$19))+1).
The correct reference in the formula column is (=SUMPRODUCT(--(J2=$J$2:$J$19),--(E2<$E$2:$E$19))+1) here helper column is "J".

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1	Week	Team	Season	Weekly Points	Cumulative points for the team	Running Rank using cumulative points and week =SUMPRODUCT(--(A2=$A$2:$A$19),--(E2<$E$2:$E$19))+1	Running Rank using cumulative points, week and season =SUMPRODUCT(--(J2=$J$2:$J$19),--(E2<$E$2:$E$19))+1			HELPER COLUMN
2	1	Patriots	2021	10	10	1	1			12021
3	1	Dolphins	2021	5	5	3	3			12021
4	1	Bills	2021	10	10	1	1			12021
5	2	Patriots	2021	5	15	2	2			22021
6	2	Bills	2021	10	20	1	1			22021
7	2	Dolphins	2021	5	10	3	3			22021
8	3	Patriots	2021	5	20	2	2			32021
9	3	Dolphins	2021	10	20	2	2			32021
10	3	Bills	2021	10	30	1	1			32021
11	1	Patriots	2020	5	5	3	3			12020
12	1	Dolphins	2020	10	10	2	2			12020
13	1	Bills	2020	15	15	1	1			12020
14	2	Patriots	2020	5	10	3	3			22020
15	2	Bills	2020	10	25	1	1			22020
16	2	Dolphins	2020	15	25	1	1			22020
17	3	patriots	2020	5	15	3	3			32020
18	3	Dolphins	2020	10	35	2	2			32020
19	3	Bills	2020	15	40	1	1			32020

I guess you were looking for the above result, in case you are looking for something different I am again reiterating please enter the desired result in the respective column so that same outcome can be obtained with the formula.
Regards