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
Photo of table here but link below too
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