Count text based on multiple criteria

[Prcntrygrl]

Okay, so, I am trying to count a win loss record for multiple teams. The team name could be in column B or Column C. So, for each team in the Wins calculation area, I want it to count if the team name is in column B and Column F is "win" AND if the team name is in column C and Column G is "win". I can get it to work with the first set, but not adding the second set of conditions. Please help.

[ConneXionLost]

Try using the SUMPRODUCT function.

Cheers,

[Cutter]

For example, in Cell C30 use:

Please Login or Register  to view this content.

[Prcntrygrl]

thanks. I have actually been trying to use sumproduct.

This is the formula I was trying to use (in cell C30 (for Brass Knuckle Betties).
=SUMPRODUCT((B2:B22=B30)*(F2:F22="win")+SUMPRODUCT(C2:C22=B30)*(G2:G22="win"))

This returns a 1 where the answer should be 2. This team has a 2-0 record.

Can you maybe tell me what I am doing wrong with this formula?

[martindwilson]

=SUMPRODUCT((B2:B22=B30)*(F2:F22="win"))+SUMPRODUCT((C2:C22=B30)*(G2:G22="win"))
or
=SUMPRODUCT((B2:B22=B30)*(F2:F22="win")+(B2:B22=B30)*(G2:G22="win"))

[Prcntrygrl]

Thank you Cutter, I was able to fix this.