COUNTIFS ?? statement to determine individual softball pitch counts

D

debbw

New Member
Joined
Feb 4, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Greetings! I'm stuck for some reason. End goal: to get the indivdual pitch call counts for each pitcher (Rise, drop, etc.) One would assume copying the formula from colum B for each pitch type & referencing B12:B3006 "*Pitcher1" would do the trick, but no such luck. Each pitch has its own letter reference: R, D, L, V, C, etc (see columns G&H for balls/strikes), so the range (as in B:2:B8 formulas show) would reference the wild card. I get a #VALUE error when I attempt: =COUNTIFS($B:$B,"*Pitcher1",$G11:$H3006,"*L*") The actual spreadsheet will have the Pitcher's name. As always, any assistance is MORE than appreciated.
Thanks in advance,
Debbie



Sample Pitching chart 2021-2022.xlsx
ABCDEFGHIJKL
1Call %Pitcher1Pitcher2Pitcher3Pitcher4
2Rise Balls4041%
3Drop Balls1111%
4Curve Balls2020%
5Fast Balls1010%
6Change Up99%
7Slow Curve88%
81st Pitch Strike2424%91500
9TOTAL PITCHES98
10
11InningPitcherBatterL/RPitch #Hit spot?BallStrikeResult
121Pitcher21L1YV3• (H)
131Pitcher21L2 R1▲
141Pitcher21L3YV3 (H)KL
153Pitcher21L1YV3 (H)
163Pitcher21L2YR1▲ (H)
173Pitcher21L3YC3■ (H)1B-4
181Pitcher11L1YL3 (H)
198Pitcher115R1 D3
208Pitcher115R2YF2 (H)
218Pitcher115R3YF2▲ (H)
228Pitcher115R4YR1• (H)KS
231Pitcher24R1YR4• (H)
241Pitcher24R2 R4
251Pitcher24R3YV3▲ (H)
261Pitcher24R4 R4■1B-9
Entry
Cell Formulas
RangeFormula
C2:C8C2=+B2/$M$34
E8E8=COUNTIFS($B:$B,"*Pitcher1",$E:$E,1,$H:$H,"?*")
G8G8=COUNTIFS($B:$B,"*Pitcher2",$E:$E,1,$H:$H,"?*")
I8I8=COUNTIFS($B:$B,"*Pitcher3",$E:$E,1,$H:$H,"?*")
K8K8=COUNTIFS($B:$B,"*Pitcher4",$E:$E,1,$H:$H,"?*")
B2B2=COUNTIF($G$12:$H$3007,"*R*")
B3B3=COUNTIF($G$12:$H$3007,"*D*")
B4B4=COUNTIF($G$12:$H$3007,"*V*")
B5B5=COUNTIF($G$12:$H$3007,"*F*")
B6B6=COUNTIF($G$12:$H$3007,"*C*")
B7B7=COUNTIF($G$12:$H$3007,"*L*")
B8B8=COUNTIFS(E:E,1,H:H,"?*")
B9B9=SUM(B2:B7)
F12:F26F12=IF(COUNTIF(G12:H12,"*H)"),"Y","")
Named Ranges
NameRefers ToCells
Strike=Entry!$H$12:$H$477F12, K8, I8, G8, E8, B2:B8
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G12:H3007Cell Valuecontains "*(H)"textNO
G12:H3007Celldoes not contain a blank value textNO
Cells with Data Validation
CellAllowCriteria
G12:H26List=Pitches!$A$2:$A$97
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Hi Debbw,

I've watched many an SF Giants game but mainly for the garlic fries and overpriced beer. Not having been raised in the USA I assume the terms used are equivalent to a googly, leg break, off-spin, cutter, etc.

For COUNTIFS the ranges used must be the same number of cells, otherwise you'll see the #VALUE error.

Something like this:

Debbw.xlsx
ABCDEFGHIJKL
1Call %Pitcher1Pitcher2Pitcher3Pitcher4
2Rise Balls642.86%1400
3Drop Balls17.14%0000
4Curve Balls428.57%0400
5Fast Balls214.29%2000
6Change Up17.14%0100
7Slow Curve17.14%1000
81st Pitch Strike428.57%1300
9TOTAL PITCHES15
10
11InningPitcherBatterL/RPitch #Hit spot?BallStrikeResult
121Pitcher21L1YV3• (H)
131Pitcher21L2 R1▲
141Pitcher21L3YV3 (H)KL
153Pitcher21L1YV3 (H)
163Pitcher21L2YR1▲ (H)
173Pitcher21L3YC3■ (H)1B-4
181Pitcher11L1YL3 (H)
198Pitcher115R1 D3
208Pitcher115R2YF2 (H)
218Pitcher115R3YF2▲ (H)
228Pitcher115R4YR1• (H)KS
231Pitcher24R1YR4• (H)
241Pitcher24R2 R4
251Pitcher24R3YV3▲ (H)
261Pitcher24R4 R4■1B-9
Sheet1
Cell Formulas
RangeFormula
C2:C8C2=+B2/$M$34
E2,K2,I2,G2E2=COUNTIFS($B$12:$B$3007,E$1,$H$12:$H$3007,"R"&"*")
E3,K3,I3,G3E3=COUNTIFS($B$12:$B$3007,E$1,$H$12:$H$3007,"D"&"*")
E4,K4,I4,G4E4=COUNTIFS($B$12:$B$3007,E$1,$H$12:$H$3007,"V"&"*")
E5,K5,I5,G5E5=COUNTIFS($B$12:$B$3007,E$1,$H$12:$H$3007,"F"&"*")
E6,K6,I6,G6E6=COUNTIFS($B$12:$B$3007,E$1,$H$12:$H$3007,"C"&"*")
E7,K7,I7,G7E7=COUNTIFS($B$12:$B$3007,E$1,$H$12:$H$3007,"L"&"*")
E8,K8,I8,G8E8=COUNTIFS($B$12:$B$3007,E$1,$E$12:$E$3007,1,$H$12:$H$3007,"<>")
B2B2=COUNTIF($G$12:$H$3007,"*R*")
B3B3=COUNTIF($G$12:$H$3007,"*D*")
B4B4=COUNTIF($G$12:$H$3007,"*V*")
B5B5=COUNTIF($G$12:$H$3007,"*F*")
B6B6=COUNTIF($G$12:$H$3007,"*C*")
B7B7=COUNTIF($G$12:$H$3007,"*L*")
B8B8=COUNTIFS($E$12:$E$3007,1,$H$12:$H$3007,"?*")
B9B9=SUM(B2:B7)
F12:F26F12=IF(COUNTIF(G12:H12,"*H)"),"Y","")
 
Upvote 0
Toadstool said:
Hi Debbw,

I've watched many an SF Giants game but mainly for the garlic fries and overpriced beer. Not having been raised in the USA I assume the terms used are equivalent to a googly, leg break, off-spin, cutter, etc.

For COUNTIFS the ranges used must be the same number of cells, otherwise you'll see the #VALUE error.

Something like this:

Debbw.xlsx
ABCDEFGHIJKL
1Call %Pitcher1Pitcher2Pitcher3Pitcher4
2Rise Balls642.86%1400
3Drop Balls17.14%0000
4Curve Balls428.57%0400
5Fast Balls214.29%2000
6Change Up17.14%0100
7Slow Curve17.14%1000
81st Pitch Strike428.57%1300
9TOTAL PITCHES15
10
11InningPitcherBatterL/RPitch #Hit spot?BallStrikeResult
121Pitcher21L1YV3• (H)
131Pitcher21L2 R1▲
141Pitcher21L3YV3 (H)KL
153Pitcher21L1YV3 (H)
163Pitcher21L2YR1▲ (H)
173Pitcher21L3YC3■ (H)1B-4
181Pitcher11L1YL3 (H)
198Pitcher115R1 D3
208Pitcher115R2YF2 (H)
218Pitcher115R3YF2▲ (H)
228Pitcher115R4YR1• (H)KS
231Pitcher24R1YR4• (H)
241Pitcher24R2 R4
251Pitcher24R3YV3▲ (H)
261Pitcher24R4 R4■1B-9
Sheet1
Cell Formulas
RangeFormula
C2:C8C2=+B2/$M$34
E2,K2,I2,G2E2=COUNTIFS($B$12:$B$3007,E$1,$H$12:$H$3007,"R"&"*")
E3,K3,I3,G3E3=COUNTIFS($B$12:$B$3007,E$1,$H$12:$H$3007,"D"&"*")
E4,K4,I4,G4E4=COUNTIFS($B$12:$B$3007,E$1,$H$12:$H$3007,"V"&"*")
E5,K5,I5,G5E5=COUNTIFS($B$12:$B$3007,E$1,$H$12:$H$3007,"F"&"*")
E6,K6,I6,G6E6=COUNTIFS($B$12:$B$3007,E$1,$H$12:$H$3007,"C"&"*")
E7,K7,I7,G7E7=COUNTIFS($B$12:$B$3007,E$1,$H$12:$H$3007,"L"&"*")
E8,K8,I8,G8E8=COUNTIFS($B$12:$B$3007,E$1,$E$12:$E$3007,1,$H$12:$H$3007,"<>")
B2B2=COUNTIF($G$12:$H$3007,"*R*")
B3B3=COUNTIF($G$12:$H$3007,"*D*")
B4B4=COUNTIF($G$12:$H$3007,"*V*")
B5B5=COUNTIF($G$12:$H$3007,"*F*")
B6B6=COUNTIF($G$12:$H$3007,"*C*")
B7B7=COUNTIF($G$12:$H$3007,"*L*")
B8B8=COUNTIFS($E$12:$E$3007,1,$H$12:$H$3007,"?*")
B9B9=SUM(B2:B7)
F12:F26F12=IF(COUNTIF(G12:H12,"*H)"),"Y","")
Click to expand...
Toadstool: 1st - I LOVE the calls (googly, leg breaker, etc.), 2nd - the beer is over priced no matter the sport or stadium; 3rd - thanks for helping, but the column G reference is non-existent. I need G & H for balls & strikes. I tried copying the H$ string in the formula to "g" but it didn't work. The result was 0 =COUNTIFS($B$12:$B$3007,E$1,$G$12:$G$3007,"R"&"*",$H$12:$H$3007,"R"&"*")

Even G12:H3007 didn't work.

If you're able to assist with the G+H, I'll be forever indebted.

And, thanks for the tip for the #VALUE error
-Debbie
 
Upvote 0
The problem with checking G and H is the same, that range has more cells than the other ranges in the COUNTIFS.

You'll need to check G then add to H.

NOTE: I've taken the liberty of using column D to simplify the formulae.

Debbw.xlsx
ABCDEFGHIJKL
1Call %Pitcher1Pitcher2Pitcher3Pitcher4
2Rise Balls642.86%R1500
3Drop Balls17.14%D1000
4Curve Balls428.57%V0400
5Fast Balls214.29%F2000
6Change Up17.14%C0100
7Slow Curve17.14%L1000
81st Pitch Strike428.57%1300
9TOTAL PITCHES15
10
11InningPitcherBatterL/RPitch #Hit spot?BallStrikeResult
121Pitcher21L1YV3• (H)
131Pitcher21L2 R1▲
141Pitcher21L3YV3 (H)KL
153Pitcher21L1YV3 (H)
163Pitcher21L2YR1▲ (H)
173Pitcher21L3YC3■ (H)1B-4
181Pitcher11L1YL3 (H)
198Pitcher115R1 D3
208Pitcher115R2YF2 (H)
218Pitcher115R3YF2▲ (H)
228Pitcher115R4YR1• (H)KS
231Pitcher24R1YR4• (H)
241Pitcher24R2 R4
251Pitcher24R3YV3▲ (H)
261Pitcher24R4 R4■1B-9
2nd
Cell Formulas
RangeFormula
C2:C8C2=+B2/$M$34
K2:K7,I2:I7,G2:G7,E2:E7E2=COUNTIFS($B$12:$B$3007,E$1,$H$12:$H$3007,$D2&"*")+COUNTIFS($B$12:$B$3007,E$1,$G$12:$G$3007,$D2&"*")
E8,K8,I8,G8E8=COUNTIFS($B$12:$B$3007,E$1,$E$12:$E$3007,1,$H$12:$H$3007,"<>")
B2:B7B2=COUNTIF($G$12:$H$3007,$D2&"*")
B8B8=COUNTIFS($E$12:$E$3007,1,$H$12:$H$3007,"<>")
B9B9=SUM(B2:B7)
F12:F26F12=IF(COUNTIF(G12:H12,"*H)"),"Y","")
 
Upvote 0
Solution
Toadstool said:
The problem with checking G and H is the same, that range has more cells than the other ranges in the COUNTIFS.

You'll need to check G then add to H.

NOTE: I've taken the liberty of using column D to simplify the formulae.

Debbw.xlsx
ABCDEFGHIJKL
1Call %Pitcher1Pitcher2Pitcher3Pitcher4
2Rise Balls642.86%R1500
3Drop Balls17.14%D1000
4Curve Balls428.57%V0400
5Fast Balls214.29%F2000
6Change Up17.14%C0100
7Slow Curve17.14%L1000
81st Pitch Strike428.57%1300
9TOTAL PITCHES15
10
11InningPitcherBatterL/RPitch #Hit spot?BallStrikeResult
121Pitcher21L1YV3• (H)
131Pitcher21L2 R1▲
141Pitcher21L3YV3 (H)KL
153Pitcher21L1YV3 (H)
163Pitcher21L2YR1▲ (H)
173Pitcher21L3YC3■ (H)1B-4
181Pitcher11L1YL3 (H)
198Pitcher115R1 D3
208Pitcher115R2YF2 (H)
218Pitcher115R3YF2▲ (H)
228Pitcher115R4YR1• (H)KS
231Pitcher24R1YR4• (H)
241Pitcher24R2 R4
251Pitcher24R3YV3▲ (H)
261Pitcher24R4 R4■1B-9
2nd
Cell Formulas
RangeFormula
C2:C8C2=+B2/$M$34
K2:K7,I2:I7,G2:G7,E2:E7E2=COUNTIFS($B$12:$B$3007,E$1,$H$12:$H$3007,$D2&"*")+COUNTIFS($B$12:$B$3007,E$1,$G$12:$G$3007,$D2&"*")
E8,K8,I8,G8E8=COUNTIFS($B$12:$B$3007,E$1,$E$12:$E$3007,1,$H$12:$H$3007,"<>")
B2:B7B2=COUNTIF($G$12:$H$3007,$D2&"*")
B8B8=COUNTIFS($E$12:$E$3007,1,$H$12:$H$3007,"<>")
B9B9=SUM(B2:B7)
F12:F26F12=IF(COUNTIF(G12:H12,"*H)"),"Y","")
Click to expand...
You, my genius friend, are AMAZING! Thank you SO MUCH! I pride myself with thinking LOGICALLY, but these formulas can blow my mind at times. I'm MUCH better with grammar, and I know what I want the outcome to be, and I can put it on paper, but to actually make it work in Excel throws me off at times. Again - thank you, Leg Breaker, (Toadstool) you have saved my day!! (yet, that grammar totally sucked)
Best,
Debbie
 
Upvote 0
You're welcome.

P.S. It's a Leg Break and it's when a ball spins from right to left against a right handed batsman.
P.P.S. I do miss shouting at Barry Bonds from the stands "My Grandma could have caught that and she's been dead 50 years!"
 
Upvote 0
You must log in or register to reply here.

Similar threads

gtd526
  • Question
Create List of Cell Values with Criteria
Replies
1
Views
250
gtd526
gtd526
B
  • Solved
Counting as one when one unique customer purchase item with multiple time in a month
Replies
1
Views
176
Eric W
Eric W
Dexir
  • Solved
Countifs query
Replies
3
Views
207
Dexir
Dexir
Z
  • Question
Massive Nested IF Statement Help Needed
Replies
1
Views
162
Zutalores
Z
P
  • Solved
Calculating shortest to longest distances between Zip Codes (using latitude and longitude)
2 3
Replies
25
Views
1K
pawcoyote
P

Forum statistics

Threads
1,215,793
Messages
6,126,934
Members
449,349
Latest member
Omer Lutfu Neziroglu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top