Need a formula that works for NHL Stats

[hockeyfan1985]

I have a formula that works for games played for W and L to add up to Games Played, I just need the formula that will work with Overtime and Shootout games added in. https://www.youtube.com/watch?v=-O5G7zR3PjI I used this to make my formulas just changed it to make it work for the NHL standings. Just need the formula for the OT, SO games. I thought I had a formula but get an error message saying "You've entered too FEW arguments for this function" Please Help been working on this formula for the whole day.

 

[DRSteele]

Further to our discussion in the other thread (https://www.mrexcel.com/forum/excel...ng-do-hockey-standings-sheet.html#post5045272), this should help move you forward on this project. The sample data is for four teams that I culled out of the entire 2018 NHL season's 1271-game results.

The source of the data in columns with yellow headers is a PowerQuery query from a website,

http://www.hockey-reference.com/leagues/NHL_2018_games.html​

The columns with orange headers contain formulas. The standings formulas in green utilise the data from that table. Copy Q2 and R2 downwards in the column.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Gm	Date	Visitor	Host	Scoreline	VScr	HScr	Result							Vis	Hst	VPts	HPts
2	1	10/04/17	Calgary Flames	Edmonton Oilers	Calgary Flames 0 - Edmonton Oilers 3 R	0	3	R							CGY	EDM	0	2
3	29	10/07/17	Edmonton Oilers	Vancouver Canucks	Edmonton Oilers 2 - Vancouver Canucks 3 R	2	3	R							EDM	VAN	0	2
4	75	10/14/17	Calgary Flames	Vancouver Canucks	Calgary Flames 5 - Vancouver Canucks 2 R	5	2	R							CGY	VAN	2	0
5	227	11/07/17	Vancouver Canucks	Calgary Flames	Vancouver Canucks 5 - Calgary Flames 3 R	5	3	R							VAN	CGY	2	0
6	267	11/14/17	Vegas Golden Knights	Edmonton Oilers	Vegas Golden Knights 2 - Edmonton Oilers 8 R	2	8	R							VGK	EDM	0	2
7	288	11/16/17	Vegas Golden Knights	Vancouver Canucks	Vegas Golden Knights 5 - Vancouver Canucks 2 R	5	2	R							VGK	VAN	2	0
8	398	12/02/17	Edmonton Oilers	Calgary Flames	Edmonton Oilers 7 - Calgary Flames 5 R	7	5	R							EDM	CGY	2	0
9	446	12/09/17	Vancouver Canucks	Calgary Flames	Vancouver Canucks 2 - Calgary Flames 4 R	2	4	R							VAN	CGY	0	2
10	508	12/17/17	Calgary Flames	Vancouver Canucks	Calgary Flames 6 - Vancouver Canucks 1 R	6	1	R							CGY	VAN	2	0
11	677	1/13/18	Edmonton Oilers	Vegas Golden Knights	Edmonton Oilers 3 - Vegas Golden Knights 2 OT	3	2	OT							EDM	VGK	2	1
12	712	1/20/18	Vancouver Canucks	Edmonton Oilers	Vancouver Canucks 2 - Edmonton Oilers 5 R	2	5	R							VAN	EDM	0	2
13	749	1/25/18	Calgary Flames	Edmonton Oilers	Calgary Flames 3 - Edmonton Oilers 4 S/O	3	4	S/O							CGY	EDM	1	2
14	764	1/30/18	Vegas Golden Knights	Calgary Flames	Vegas Golden Knights 4 - Calgary Flames 2 R	4	2	R							VGK	CGY	2	0
15	888	2/15/18	Edmonton Oilers	Vegas Golden Knights	Edmonton Oilers 1 - Vegas Golden Knights 4 R	1	4	R							EDM	VGK	0	2
16	928	2/21/18	Calgary Flames	Vegas Golden Knights	Calgary Flames 3 - Vegas Golden Knights 7 R	3	7	R							CGY	VGK	0	2
17	944	2/23/18	Vancouver Canucks	Vegas Golden Knights	Vancouver Canucks 3 - Vegas Golden Knights 6 R	3	6	R							VAN	VGK	0	2
18	1072	3/13/18	Edmonton Oilers	Calgary Flames	Edmonton Oilers 0 - Calgary Flames 1 R	0	1	R							EDM	CGY	0	2
19	1113	3/18/18	Calgary Flames	Vegas Golden Knights	Calgary Flames 0 - Vegas Golden Knights 4 R	0	4	R							CGY	VGK	0	2
20	1128	3/20/18	Vancouver Canucks	Vegas Golden Knights	Vancouver Canucks 1 - Vegas Golden Knights 4 R	1	4	R							VAN	VGK	0	2
21	1198	3/29/18	Edmonton Oilers	Vancouver Canucks	Edmonton Oilers 1 - Vancouver Canucks 2 R	1	2	R							EDM	VAN	0	2
22	1208	3/31/18	Edmonton Oilers	Calgary Flames	Edmonton Oilers 2 - Calgary Flames 3 R	2	3	R							EDM	CGY	0	2
23	1236	4/03/18	Vegas Golden Knights	Vancouver Canucks	Vegas Golden Knights 5 - Vancouver Canucks 4 S/O	5	4	S/O							VGK	VAN	2	1
24	1242	4/05/18	Vegas Golden Knights	Edmonton Oilers	Vegas Golden Knights 3 - Edmonton Oilers 4 R	3	4	R							VGK	EDM	0	2
25	1259	4/07/18	Vegas Golden Knights	Calgary Flames	Vegas Golden Knights 1 - Calgary Flames 7 R	1	7	R							VGK	CGY	0	2
26	1262	4/07/18	Vancouver Canucks	Edmonton Oilers	Vancouver Canucks 2 - Edmonton Oilers 3 S/O	2	3	S/O							VAN	EDM	1	2
27
28	TEAM	GP	W	L	OT	Points	GF	GA	Diff	ROW
29	CGY	13	6	6	1	13	42	42	0	6
30	EDM	13	8	5	0	16	43	32	11	6
31	VAN	12	3	7	2	8	29	49	-20	3
32	VGK	12	8	3	1	17	47	38	9	7
33	Sum	50	25	21	4	54	161	161	0	22
Sheet54

Cell Formulas
Range	Formula
Q2	=IF(E2="","",IF(F2>G2,2,IF(OR(H2="S/O",H2="OT"),1,0)))
R2	=IF(E2="","",IF(G2>F2,2,IF(OR(H2="S/O",H2="OT"),1,0)))
B29	=SUM(C29:E29)
B30	=SUM(C30:E30)
B31	=SUM(C31:E31)
B32	=SUM(C32:E32)
B33	=SUM(B29:B32)
C29	=COUNTIFS($O$2:$O$26,$A29,$Q$2:$Q$26,2)+COUNTIFS($P$2:$P$26,$A29,$R$2:$R$26,2)
C30	=COUNTIFS($O$2:$O$26,$A30,$Q$2:$Q$26,2)+COUNTIFS($P$2:$P$26,$A30,$R$2:$R$26,2)
C31	=COUNTIFS($O$2:$O$26,$A31,$Q$2:$Q$26,2)+COUNTIFS($P$2:$P$26,$A31,$R$2:$R$26,2)
C32	=COUNTIFS($O$2:$O$26,$A32,$Q$2:$Q$26,2)+COUNTIFS($P$2:$P$26,$A32,$R$2:$R$26,2)
C33	=SUM(C29:C32)
D29	=COUNTIFS($O$2:$O$26,$A29,$Q$2:$Q$26,0)+COUNTIFS($P$2:$P$26,$A29,$R$2:$R$26,0)
D30	=COUNTIFS($O$2:$O$26,$A30,$Q$2:$Q$26,0)+COUNTIFS($P$2:$P$26,$A30,$R$2:$R$26,0)
D31	=COUNTIFS($O$2:$O$26,$A31,$Q$2:$Q$26,0)+COUNTIFS($P$2:$P$26,$A31,$R$2:$R$26,0)
D32	=COUNTIFS($O$2:$O$26,$A32,$Q$2:$Q$26,0)+COUNTIFS($P$2:$P$26,$A32,$R$2:$R$26,0)
D33	=SUM(D29:D32)
E29	=COUNTIFS($O$2:$O$26,$A29,$Q$2:$Q$26,1)+COUNTIFS($P$2:$P$26,$A29,$R$2:$R$26,1)
E30	=COUNTIFS($O$2:$O$26,$A30,$Q$2:$Q$26,1)+COUNTIFS($P$2:$P$26,$A30,$R$2:$R$26,1)
E31	=COUNTIFS($O$2:$O$26,$A31,$Q$2:$Q$26,1)+COUNTIFS($P$2:$P$26,$A31,$R$2:$R$26,1)
E32	=COUNTIFS($O$2:$O$26,$A32,$Q$2:$Q$26,1)+COUNTIFS($P$2:$P$26,$A32,$R$2:$R$26,1)
E33	=SUM(E29:E32)
F29	=C29*2+E29
F30	=C30*2+E30
F31	=C31*2+E31
F32	=C32*2+E32
F33	=SUM(F29:F32)
G29	=SUMIFS($F$2:$F$26,$O$2:$O$26,$A29)+SUMIFS($G$2:$G$26,$P$2:$P$26,$A29)
G30	=SUMIFS($F$2:$F$26,$O$2:$O$26,$A30)+SUMIFS($G$2:$G$26,$P$2:$P$26,$A30)
G31	=SUMIFS($F$2:$F$26,$O$2:$O$26,$A31)+SUMIFS($G$2:$G$26,$P$2:$P$26,$A31)
G32	=SUMIFS($F$2:$F$26,$O$2:$O$26,$A32)+SUMIFS($G$2:$G$26,$P$2:$P$26,$A32)
G33	=SUM(G29:G32)
H29	=SUMIFS($F$2:$F$26,$P$2:$P$26,$A29)+SUMIFS($G$2:$G$26,$O$2:$O$26,$A29)
H30	=SUMIFS($F$2:$F$26,$P$2:$P$26,$A30)+SUMIFS($G$2:$G$26,$O$2:$O$26,$A30)
H31	=SUMIFS($F$2:$F$26,$P$2:$P$26,$A31)+SUMIFS($G$2:$G$26,$O$2:$O$26,$A31)
H32	=SUMIFS($F$2:$F$26,$P$2:$P$26,$A32)+SUMIFS($G$2:$G$26,$O$2:$O$26,$A32)
H33	=SUM(H29:H32)
I29	=G29-H29
I30	=G30-H30
I31	=G31-H31
I32	=G32-H32
I33	=SUM(I29:I32)
J29	=C29-(COUNTIFS($O$2:$O$26,$A29,$H$2:$H$26,"=S/O",$Q$2:$Q$26,2)+COUNTIFS($P$2:$P$26,$A29,$H$2:$H$26,"=S/O",$R$2:$R$26,2))
J30	=C30-(COUNTIFS($O$2:$O$26,$A30,$H$2:$H$26,"=S/O",$Q$2:$Q$26,2)+COUNTIFS($P$2:$P$26,$A30,$H$2:$H$26,"=S/O",$R$2:$R$26,2))
J31	=C31-(COUNTIFS($O$2:$O$26,$A31,$H$2:$H$26,"=S/O",$Q$2:$Q$26,2)+COUNTIFS($P$2:$P$26,$A31,$H$2:$H$26,"=S/O",$R$2:$R$26,2))
J32	=C32-(COUNTIFS($O$2:$O$26,$A32,$H$2:$H$26,"=S/O",$Q$2:$Q$26,2)+COUNTIFS($P$2:$P$26,$A32,$H$2:$H$26,"=S/O",$R$2:$R$26,2))
J33	=SUM(J29:J32)

 

[gmhumphr]

This seem like a fun problem set to work on. We'll need more info to help you though (especially for folks who aren't sports fans).

What does your data look like? What kind of calculation are you trying to do?

If you provide screenshots and a description of how you would like to operate on that data to get a specific result, folks on the board would be happy to help.

Example:
Data input is a row for each game, with Home team, away team, home goals, away goals, overtime home goals, overtime away goals, shootout home goals, shootout away goals, etc.
Desired output count of SO goals.

If your input set has the goals scored by type, continue with what the video said and use sumif fomulas and just change the specific columns you want to sum on (the OT/SO columns). If you need to modify the rank, you need to explain what the criteria is you want to modify on.

 

[hockeyfan1985]

The formula Dr. Steel posted is what I needed, I seen a post like his early but was using the NHL website as the power link and it was not working. Now this working except every team has 82 Losses, instead of 0 to start the season. Thank you for the help.