Need help with countiif formula

S

supercell

I posted a message similar to this a couple weeks ago and received a good solution, but I neglected to include an important piece of information, which currently has me stumped.

I’m using Excel to track stats for a bowling league. I would like to automate tracking individual wins and losses. Per the previous suggestion, I’m using the following array formula: =COUNTIF($E$2:$E$35,">"& $N$2:$N$35). However, I also need to take into account the handicap, so the formula needs to count if the score plus the handicap is greater than the other score plus handicap. I’ve tried doing =COUNTIF($D$2:$D$35 + $E$2:$E$35,">" & $M$2:$M$35 + $N$2:$N$35), but that does not perform the calculation properly. Thanks in advance & Happy New Year.
 
B

Bob Phillips

Try this

=SUMPRODUCT(($D$2:$D$35>$M$2:$M$35)*($E$2:$E$35>$N$2:$N$35))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

supercell said:
I posted a message similar to this a couple weeks ago and received a good
Click to expand...
solution, but I neglected to include an important piece of information,
which currently has me stumped.
I'm using Excel to track stats for a bowling league. I would like to
Click to expand...
automate tracking individual wins and losses. Per the previous suggestion,
I'm using the following array formula: =COUNTIF($E$2:$E$35,">"&
$N$2:$N$35). However, I also need to take into account the handicap, so the
formula needs to count if the score plus the handicap is greater than the
other score plus handicap. I've tried doing =COUNTIF($D$2:$D$35 +
$E$2:$E$35,">" & $M$2:$M$35 + $N$2:$N$35), but that does not perform the
calculation properly. Thanks in advance & Happy New Year.
 
S

supercell

Thanks for the suggestion, but that returned a 0 - unless I've done something wrong. Here's an example of what I want to do:

PLAYER 1 PLAYER 2
A B C D
Score Hdcp Score Hdcp
150 15 170 30
200 5 175 35
189 3 180 5

Columns A&B need to be summed and compared to the sum of columns C&D. Whichever is higher gets the win. In this particular example, PLAYER 1 should have a record of 1 win and 2 losses. Thanks again in advance.
 
B

Bob Phillips

Sorry, I misunderstood what you were asking for.

Try this instead, I think it now does what you want.

=SUM(--($D$2:$D$35+$E$2:$E$35>$M$2:$M$35+$N$2:$N$35))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

supercell said:
Thanks for the suggestion, but that returned a 0 - unless I've done
Click to expand...
something wrong. Here's an example of what I want to do:
PLAYER 1 PLAYER 2
A B C D
Score Hdcp Score Hdcp
150 15 170 30
200 5 175 35
189 3 180 5

Columns A&B need to be summed and compared to the sum of columns C&D.
Click to expand...
Whichever is higher gets the win. In this particular example, PLAYER 1
should have a record of 1 win and 2 losses. Thanks again in advance.
 
Top