Formula for counting with two criteria, name and counting for baseball stats

[elketa1252]

Guys,

I have a baseball team and would like a formula to keep the stats. For example, I would like a formula in Cell F3 that counts how many 1b(from Cell F2) does Salvador Martinez(Cell B3) has in the range B33:F:40. So this formula would need to match B3 and find the rows where Salvador Martinez is and add all the 1b. The return would be 3, the ones in yellow. I would use the formula for the other players and for the different stats found in ranges F:2N2. Just to throw another example, these are in red for Jose Perez HR, Cell I7, this formula would return 2 here. Any help would be much appreciated. I have tried all types of formula, index match sumproduct and just cannot do it. Sorry for my english, it's my second language.

Thank you. And I tried upload a picture but it's asking me for an URL instead of uploading from my computer.


B C D E F G H I J K L M N O P Q R S

2 Name	PA	AB	H	1b	2b	3b	HR	ROE	SACf	BB	GDP	Ks	RBI	RUNS	BA	OB%	Slug%
3 Salvador Martinez	0	0	0	0	0	0	0	0	0	0	0	0	0	0	#N/A	#N/A	#N/A
4 Miguel Rosario	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.000	0.000	0.000
5 Frank Guzman	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.000	0.000	0.000
6 Galindo Gomez	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.000	0.000	0.000
7 Jose Perez	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.000	0.000	0.000
33 Salvador Martinez 2b 1b 2b hr 34 Yoel Martinez 2b hr 2b 1b 35 Jose Perez 2b 1b hr hr 36 Miguel Rosario hr 2b 3b 3b 37 Frank Guzman 1b 2b 3b 2b 38 Salvador Martinez 3b 2b 3b 3b 39 Galindo Gomez 2b 3b 3b 2b 40 Salvador Martinez 2b 1b 1b hr <tbody> </tbody>

<tbody>
</tbody>

 

[sheetspread]

This?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Name	PA	AB	H	1b	2b	3b	HR	ROE	SACf	BB	GDP	Ks	RBI	RUNS	BA	OB%	Slug%
2	Salvador Martinez	0	0	0	3	4	3	2	0	0	0	0	0	0	0	0	0	0
3	Miguel Rosario	0	0	0	0	1	2	1	0	0	0	0	0	0	0	0	0	0
4	Frank Guzman	0	0	0	1	2	1	0	0	0	0	0	0	0	0	0	0	0
5	Galindo Gomez	0	0	0	0	2	2	0	0	0	0	0	0	0	0	0	0	0
6	Jose Perez	0	0	0	1	1	0	2	0	0	0	0	0	0	0	0	0	0
7
8
9	Salvador Martinez	2b	1b	2b	hr
10	Yoel Martinez	2b	hr	2b	1b
11	Jose Perez	2b	1b	hr	hr
12	Miguel Rosario	hr	2b	3b	3b
13	Frank Guzman	1b	2b	3b	2b
14	Salvador Martinez	3b	2b	3b	3b
15	Galindo Gomez	2b	3b	3b	2b
16	Salvador Martinez	2b	1b	1b	hr
Sheet2

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(($A$9:$A$16=$A2)*($B$9:$E$16=B$1))

 

[elketa1252]

Hey wow thank you very much but I can't get it to work. I am pretty sure that might be the solution because most of the solutions online i searched brought me to something like this but it doesn't work for me. Im using excel 2016. could that be it? This is the formula I pasted specifically in Cell F3 and it just doesn't work . =SUMPRODUCT(($B$33:$B$40=$B3)*($C$33:$F$40=F$2))

Do you have the file because all the results you got is exactly what I need.

Thank you again for all your help.

 

[elketa1252]

Sorry I just realized your table is setup different than mine, the columns and rows, but the formula I pasted in my reply is in the right location in my file. Don't want to confuse you.

 

[sheetspread]

your table is setup different than mine

This any better?

Excel 2010
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	Name	PA	AB	H	1b	2b	3b	HR	ROE	SACf	BB	GDP	Ks	RBI	RUNS	BA	OB%	Slug%
3	Salvador Martinez	0	0	0	3	4	3	2	0	0	0	0	0	0	0	0	0	0
4	Miguel Rosario	0	0	0	0	1	2	1	0	0	0	0	0	0	0	0	0	0
5	Frank Guzman	0	0	0	1	2	1	0	0	0	0	0	0	0	0	0	0	0
6	Galindo Gomez	0	0	0	0	2	2	0	0	0	0	0	0	0	0	0	0	0
7	Jose Perez	0	0	0	1	1	0	2	0	0	0	0	0	0	0	0	0	0
31
32
33	Salvador Martinez	2b	1b	2b	hr
34	Yoel Martinez	2b	hr	2b	1b
35	Jose Perez	2b	1b	hr	hr
36	Miguel Rosario	hr	2b	3b	3b
37	Frank Guzman	1b	2b	3b	2b
38	Salvador Martinez	3b	2b	3b	3b
39	Galindo Gomez	2b	3b	3b	2b
40	Salvador Martinez	2b	1b	1b	hr
Sheet2

Cell Formulas
Range	Formula
C3	=SUMPRODUCT(($B$33:$B$40=$B3)*($C$33:$F$40=C$2))

 

[elketa1252]

Sorry maybe I did not explain myself well. I have the formula and understand it, it's just that excel doesn't do the calculation, it just returns 0, it does not do the calculation for me. I tried it as an array formula too with ctrl shift and enter and nothing, it just returns 0. I even changed the locations of all the cells to match yours and copied and pasted the formula and nothing. It's a file done in a 1997-2003 excel format but Im using 2016 excel. Could that be it since Im pretty sure I have everything the right way.

 

[Fluff]

Have you checked to see if Calculation is set to manual or automatic?

 

[kweaver]

Be sure that you are dragging the formula in B2 across and down (using the 1st example provided).

 

[elketa1252]

Have you checked to see if Calculation is set to manual or automatic?

Thanks for the reply. I just checked in the options > formulas and it's set to automatic. I'm sorry for the back and forth. It's supposed to be automatic I imagine right?

 

[elketa1252]

Be sure that you are dragging the formula in B2 across and down (using the 1st example provided).

It's not when I'm dragging, it's just that the first formula doesn't even work. I have a little knowledge of excel and I know that $ is to lock the cell if that's what you mean. Once I get a cell to work I will know how to make it work every where but it doesn't even work on the first cell. Thanks for your help.