如何用单个Excel公式统计先手玩家获胜次数并适配新增数据

Hey there! I see you're trying to count how many times the first player (column D) won their game (column E) in your scoreboard, and you need a formula that automatically updates when you add new rows. Let's break this down.

Why Your Original Formula Didn't Work #

The =COUNTIF($E$3:$E, $D$3:$D) formula counts how many times any value from column D appears in column E overall—it doesn't check if the first player (D) matches the winner (E) in the same row. That's why you got 7 instead of the expected 3.

The Correct Single Formula #

To count rows where the first player (D) is exactly the winner (E), and have it auto-update when you add new games, use this formula:

=SUMPRODUCT(--($D:$D=$E:$E), --($D:$D<>""), --($E:$E<>""))

How This Works:

• $D:$D=$E:$E: Checks each row to see if the first player matches the winner, returning TRUE or FALSE for every row.
• --(...): Converts those TRUE/FALSE values to 1/0 (since SUMPRODUCT works with numbers).
• --($D:$D<>"") and --($E:$E<>""): Makes sure we ignore empty rows at the bottom of your sheet (so new blank rows don't mess up the count).
• SUMPRODUCT adds up all the 1s from rows where all three conditions are met—exactly the count of first player wins.

Testing It Against Your Data #

Looking at your scoreboard, the rows where D=E are:

• Row 4: Kevin (first player) = Kevin (winner)
• Row 6: Kevin (first player) = Kevin (winner)
• Row 12: M (first player) = M (winner)

That's 3 wins, which matches your expected result. And since we're using full column references ($D:$D instead of $D$3:$D$16), any new game you add to the rows below will automatically be included in the count.

Alternative for Google Sheets (or Excel) #

If you prefer using COUNTIFS, this works too:

=COUNTIFS($D:$D, $E:$E, $D:$D, "<>", $E:$E, "<>")

It does the same thing—counts rows where D equals E, and both columns aren't empty.

内容的提问来源于stack exchange，提问作者Matthew Woo