I have been able to accomplish everything I want with excel up until now. This is the last function I need. After 4-5 hours unsuccessfully struggling with various ways to set this up, I’m turning to this community for help.
Range 1: Column 1 named “Date”
Criteria 1: Specific dates
Range 2: Column 2 named “Batter”
Criteria 2: specific batters
Range 3: Column 3 named “First_runner”
Criteria: Any text
Range 4: Coumn 4 named “Second_runner”
Criteria: Any text
Range 5: Column 5 named “Third_runner”
Criteria: Any text
What I am trying to find is the number of at bats given specific dates and specific batters with any runners on base. The problem is that the Countifs function counts the text in columns 3, 4, and 5 multiple times.
Example:
=COUNTIFS(Date,G4,batter,H4,first_runner,"?*")+COUNTIFS(Date,G4,batter,H4,second_runner,"?*")+COUNTIFS(Date,G4,batter,H4,third_runner,"?*")
The above function returns a count of “5.” I need for it to return a count of “3” because “posaj001” batted a total of 4 times on the criteria date, but he only batted with men on base a total of 3 times.
This formula also needs to scale. I have tried a gazillion different methods for subtracting certain criteria, but I can never get it to come out right for every single situation.
Is there an easy way to count how many times any text appears in any order in any of the runner columns using the date and batter criteria also? I suppose I could combine/concentate columns 3, 4, 5, but that is far from ideal.
See attachment for details relating to the references above…
Bookmarks