Summing Multiple Columns and Rows

[AllAboutAlly]

I've generated a worksheet for pari-mutual betting and I've hit a road-block. I added results to range L1:N4. I need a formula in cells M7 and M8 that will search for winning bets and calculate the total payout due to the bettor. This worksheet has many more rows and several more columns. I made the odds 1/1 to simplify.
Can anyone help?


Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	LAST NAME/FIRST NAME	TEAM NUMBER	WAGER TYPE	AMOUNT	TEAM NUMBER	WAGER TYPE	AMOUNT	TEAM NUMBER	WAGER TYPE	AMOUNT		RESULTS	TEAM MEMBER	ODDS
2	Ambler, Rosendo	23	FLIGHT WIN	5	19	OVERALL SHOW	25	34	FLIGHT PLACE	10		OVERALL WIN	70	1/1
3	Burgo, Mose	66	FLIGHT PLACE	10	41	FLIGHT WIN	30	10	FLIGHT SHOW	15		OVERALL PLACE	14	1/1
4	Higham, Herta	83	FLIGHT SHOW	15	64	FLIGHT PLACE	35	89	OVERALL WIN	20		OVERALL SHOW	38	1/1
5	Elvin, Domingo	70	OVERALL WIN	20	24	FLIGHT SHOW	40	14	OVERALL PLACE	25
6	Shiffer, Alene	90	OVERALL PLACE	25	20	OVERALL WIN	45	17	OVERALL SHOW	30		BETTOR	PAYOUT
7	Tome, Norberto	21	OVERALL SHOW	30	14	OVERALL PLACE	50	9	FLIGHT WIN	35		Elvin, Domingo	45
8	Steveson, Mignon	27	FLIGHT WIN	35	38	OVERALL SHOW	5	90	FLIGHT PLACE	40		Vrba, Benito	40
9	Hoehne, Marti	66	FLIGHT PLACE	40	62	FLIGHT WIN	10	50	FLIGHT SHOW	45
10	Bingaman, Elba	17	FLIGHT SHOW	45	4	FLIGHT PLACE	15	71	OVERALL WIN	50
11	Wollard, Kathleen	30	OVERALL WIN	50	31	FLIGHT SHOW	20	59	OVERALL PLACE	5
12	Vrba, Benito	14	OVERALL PLACE	5	70	OVERALL WIN	25	38	OVERALL SHOW	10
13	Taitt, Melissa	59	OVERALL SHOW	10	38	OVERALL SHOW	25	46	FLIGHT WIN	15
14	Grasty, Ismael	13	FLIGHT WIN	15	87	FLIGHT WIN	30	85	FLIGHT PLACE	20
15	Valenzuela, Tesha	5	FLIGHT PLACE	20	35	FLIGHT PLACE	35	86	FLIGHT SHOW	25
16	Cowan, Lennie	83	FLIGHT SHOW	25	91	FLIGHT SHOW	40	54	OVERALL WIN	30
17	Sanchez, Janet	3	OVERALL WIN	30	44	OVERALL WIN	45	14	OVERALL PLACE	35
18	Griffin, Brian	91	OVERALL PLACE	35	95	OVERALL PLACE	50	79	OVERALL SHOW	40
19	Bailey, Keith	47	OVERALL SHOW	40	84	OVERALL SHOW	5	49	FLIGHT WIN	45
20	Mitchell, Jeremy	39	FLIGHT WIN	45	23	FLIGHT WIN	10	8	FLIGHT PLACE	50
21
22

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

Sheet1​

 

[jtakw]

Hi,

Would summing row by row work for you?

K2 formula with your criterion hard-coded.
L2 formula uses cell references for criterion.

Either formula copied down:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	LAST NAME/FIRST NAME	TEAM NUMBER	WAGER TYPE	AMOUNT	TEAM NUMBER	WAGER TYPE	AMOUNT	TEAM NUMBER	WAGER TYPE	AMOUNT	PAYOUT	PAYOUT	RESULTS	TEAM MEMBER	ODDS
2	Ambler, Rosendo	23	FLIGHT WIN	5	19	OVERALL SHOW	25	34	FLIGHT PLACE	10	0	0	OVERALL WIN	70	1/1
3	Burgo, Mose	66	FLIGHT PLACE	10	41	FLIGHT WIN	30	10	FLIGHT SHOW	15	0	0	OVERALL PLACE	14	1/1
4	Higham, Herta	83	FLIGHT SHOW	15	64	FLIGHT PLACE	35	89	OVERALL WIN	20	0	0	OVERALL SHOW	38	1/1
5	Elvin, Domingo	70	OVERALL WIN	20	24	FLIGHT SHOW	40	14	OVERALL PLACE	25	45	45
6	Shiffer, Alene	90	OVERALL PLACE	25	20	OVERALL WIN	45	17	OVERALL SHOW	30	0	0	BETTOR	PAYOUT
7	Tome, Norberto	21	OVERALL SHOW	30	14	OVERALL PLACE	50	9	FLIGHT WIN	35	50	50	Elvin, Domingo	45
8	Steveson, Mignon	27	FLIGHT WIN	35	38	OVERALL SHOW	5	90	FLIGHT PLACE	40	5	5	Vrba, Benito	40
9	Hoehne, Marti	66	FLIGHT PLACE	40	62	FLIGHT WIN	10	50	FLIGHT SHOW	45	0	0
10	Bingaman, Elba	17	FLIGHT SHOW	45	4	FLIGHT PLACE	15	71	OVERALL WIN	50	0	0
11	Wollard, Kathleen	30	OVERALL WIN	50	31	FLIGHT SHOW	20	59	OVERALL PLACE	5	0	0
12	Vrba, Benito	14	OVERALL PLACE	5	70	OVERALL WIN	25	38	OVERALL SHOW	10	40	40
13	Taitt, Melissa	59	OVERALL SHOW	10	38	OVERALL SHOW	25	46	FLIGHT WIN	15	25	25
14	Grasty, Ismael	13	FLIGHT WIN	15	87	FLIGHT WIN	30	85	FLIGHT PLACE	20	0	0
15	Valenzuela, Tesha	5	FLIGHT PLACE	20	35	FLIGHT PLACE	35	86	FLIGHT SHOW	25	0	0
16	Cowan, Lennie	83	FLIGHT SHOW	25	91	FLIGHT SHOW	40	54	OVERALL WIN	30	0	0
17	Sanchez, Janet	3	OVERALL WIN	30	44	OVERALL WIN	45	14	OVERALL PLACE	35	35	35
18	Griffin, Brian	91	OVERALL PLACE	35	95	OVERALL PLACE	50	79	OVERALL SHOW	40	0	0
19	Bailey, Keith	47	OVERALL SHOW	40	84	OVERALL SHOW	5	49	FLIGHT WIN	45	0	0
20	Mitchell, Jeremy	39	FLIGHT WIN	45	23	FLIGHT WIN	10	8	FLIGHT PLACE	50	0	0
Sheet634

Cell Formulas
Range	Formula
K2	=SUM(SUMIFS(D2:J2,C2:I2,{"OVERALL WIN","OVERALL PLACE","OVERALL SHOW"},B2:H2,{70,14,38}))
L2	=SUMIFS(D2:J2,B2:H2,N$2,C2:I2,M$2)+SUMIFS(D2:J2,B2:H2,N$4,C2:I2,M$4)+SUMIFS(D2:J2,B2:H2,N$3,C2:I2,M$3)