Forum Discussion
multiple count using different rows comparing multiple columns
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
| A | B | C | D |
| john | 2 | yes | |
| john | 2 | ||
| john | 2 | yes | |
| rick | 1.5 | ||
| john | 1 | ||
| rick | 0.5 | ||
| john | 0 | ||
| john | 1 | yes | yes |
| rick | 0.5 | ||
| john | 0 |
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
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.
