multiple count using different rows comparing multiple columns

Brass Contributor

some assistance please!

i posted something prior but i think i confused the matter. maybe this clears it up

 

count how many times a the name in column A has a "yes" in column C and a "yes" in column D that occurred at the same number as column B
the answer here should be 1 for John
but if I change B9 to 2. then the answer should be 2

 

ABCD
john2 yes
john2  
john2yes 
rick1.5  
john1  
rick0.5  
john0  
john1yesyes
rick0.5  
john0  

 

i tried multiple ways and im left at this

=SUMPRODUCT(("yes"=data[C]=data[B]=INDEX(data[B],MATCH("yes",data[C])))*("yes"=data[D]=data[B]=INDEX(data[B],MATCH("yes",data[D]))))

 

it just isnt working in so many ways i try. is this possible without vlookup? or offset? i have a big sheet so i just would love to have some direction on how to achieve this problem

 

attached the online version

 

Book1.xlsx

16 Replies
Why it would 2 when you change B9 cell to 2? Why not 1 or 3?
because column C's "yes" happens when column B is the same number when column D is "yes"
What happens if B11 turns to 2?

I think this is still pretty confusing. You'll need to come up with more examples under varying conditions to make your underlying heuristic (rule) unambiguously clear, before it can be turned into a reliable formula.
B11 does not contain a yes in column C or D so it wouldnt change anything

its for a pool match. column B is "inning", column C is "ball in hand", column D is "win"

if a player gets ball in hand in the same inning as his win then i want to count.

@rangelsammon 

You still are leaving many things unexplained. You think they're obvious things, which is why you don't think they need explanation. But they do.

 

You think you gave a full explanation to @Harun24HR in response to his question. But you actually gave only a partial answer.

 

Fill out your online sample sheet with more data, realistic data, and give more examples of what the rules you're wanting to program would yield. 

most of me thinks if i explain the question more it might confuse some but im going to attempt here.

This is data from a pool match.
In pool there are games which Is column A
every change of turn by a player can change the inning by .5
as you can see in A11 it is Johns turn in the first game and the inning is 0
then its rick turn and the inning is .5
and on ricks turn (row 10) he fouls and that is reflected in column E
because he fouls it is now johns turn and now the inning is 1 because the change in turn reflects a change of .5 by inning
whenever someone fouls it is ball in hand and reflected in column D
when a player gets ball in hand I want to know if that player ultimately wins the game without giving the turn back to the other player
a win is reflected in column F
so here John won twice without giving the ball back after ball in hand
in game 1 he won on the same turn he received ball in hand
in game 2 he won 2 shots after ball in hand
each win occurred during the same inning as the ball in hand.

i really appreciate everyones assistance. im trying to achieve this with just a formula and not adding additional columns to count like a helper column.

https://1drv.ms/x/s!AnFi6uGE1reki2mZBJz9KxQLchAK?e=D16fGf

@rangelsammon Just following the thread this time. :face_with_tears_of_joy: 

 

Really curious to see the final answer.  

lol your help did not go unnoticed ;p thank you very much for the assistance. i hope my explanation makes more sense. i have been messing with this for days now. i keep thinking i figured it out. nope. i just need the inning to equal the same inning as wins & ball in hand. but i dont know if i can have that number of inning be different and count. im trying arrays

@rangelsammon Just to clarify, is it a requirement that the "ball in hand" and "win" are NOT in the same row?  Sorry if you answered this already.  

 

I can see by example 1 (Game 2, Inning 2), they are on separate rows.  But doesn't row 9 meet the criteria of winning within the same inning (Game 1, Inning 1) after getting ball in hand?  You mentioned John's tally would increase if B9 changed to 2, however, it's a separate game so this is what's confusing me.  

 

Also, are you looking for one count per player that meets the conditions regardless of the number of games?  Or are you looking for a summary/count by each game?

 

I think I have a solution, I'm serious this time, but completely different approach than last time.   

 

Dexter 

 

DexterG_III_0-1663375974253.png

 

yes 1 count per player. the way you highlighted helps. the red would be 1. the yellow would be 1. so total 2. to make it easier to test. if you take the win "yes" out of the red highlighted above then the total would be 1. and the same total if you took the win out of the yellow.

@rangelsammon 

 

Since @DexterG_III thinks he has a solution, I'm going to wait and see what he comes up with. 

 

In the meantime, may I do you the favor of pointing out one key feature that might explain some of the confusion here: it was only in reading this final description that I realized the history of this game goes from the bottom up!! I do believe it would be "normal"--though I may be just making a massive assumption--based on history of transactions (the kind of database with which I'm most familiar) when dates are entered they typically begin at the top and go down as history progresses. In that manner, I would expect the game--its scoring, its innings, its whatever-- to begin at the top and proceed downward.  

 

And if I'm not mistaken, those numbers in Column B actually have no direct relationship to the scores; is that right?! They're just time markers (innings)... 

 

Beyond that, what you've explained is how the "yes" entries get made. What I was looking for, and perhaps it's @DexterG_III 's explanation that does this--what I was looking for was a clearer explanation of how you waned to go from the data to the results you want; not an explanation of how the entries got there in the first place. I was looking for clarity that begins with the data--whatever it represents--the rules that would underly the formula, in other words. The rules that would answer the questions you got at the start and explain how you got the ones and twos for John....

 

Does that make sense? Or am I just adding confusion of my own?

correct column B has no relationship to the score. it only states the in ing the shot/moment occured.

it makes sense what ya mean. overall. i wasnt clear;p but im thankful for the community here

@rangelsammon Okay this is not a formula but I do believe it calculates the correct count.  The TableIn Query reads the table data and filters all rows only for wins = yes. 

 

The TableOut query does the same except with BallInHand = yes.   The 1st table is then merged with the second looking for a match on all three critical columns.   If a player has both a BallInHand = Yes and a Win = Yes for the same game and the same inning, then it returns a 1 in the count column.  As we know, this could return multiple rows so the last step is to group by player and sum the count column.  

 

The bad news is you must press the refresh button on the TableOut query in order for the summary (K1:L2) to be updated.  The good news is the refresh could be automated via VBA based on cell updates or something similar.  

 

I know you're looking for a real time formula and it's possible someone else can provide that answer.  As a backup, this might work.  

 

DexterG_III_0-1663383221179.png

 

thanks again for your assistance. ya i really would love a formula in real time but your assistance means alot. i think im going to resort to a conditional(helper) column to achieve the count

@rangelsammon No worries.  But if you can deselect my response as an answer please do if it's not what you were looking for.  I know there are many in this forum with skills greater than mine and willing to help.  I assume many will filter for unanswered questions.   If you're looking for that formula, your best shot is to keep this thread open and I'd take no offense in you doing so.  

 

 

btw, i ended up doing conditional argument vs counting in one cell.