Need help with countiif formula

[Guest]

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.

 

[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)

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.

 

[Guest]

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.

 

[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)

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.

 

[Guest]

That did it! Thanks much!