如何统计Excel中Job2行Chris列且值>2的单元格数量？

Correct Formulas #

Using SUMPRODUCT #

Your original formula had two key issues: incorrect combination of row/column conditions (using addition instead of multiplication) and invalid syntax for value comparison. Here's the fixed version:
=SUMPRODUCT((A2:A6="Job 2")*(B1:E1="Chris")*(B2:E6>2))

Breakdown of the logic:

• (A2:A6="Job 2"): Generates an array of TRUE/FALSE values where rows match "Job 2"
• (B1:E1="Chris"): Generates an array of TRUE/FALSE values where the column header is "Chris"
• (B2:E6>2): Generates an array of TRUE/FALSE values where cell values are greater than 2
• Multiplying these arrays converts TRUE to 1 and FALSE to 0; SUMPRODUCT sums the 1s where all three conditions are satisfied.

Using COUNTIFS (Simpler Alternative) #

Since the "Chris" column is fixed (column B), you can directly target that range with COUNTIFS:
=COUNTIFS(A2:A6,"Job 2",B2:B6,">2")

This formula counts cells in column B (Chris) where the corresponding row in column A is "Job 2" and the value in B is greater than 2.

Expected Result #

Both formulas will return 2, which matches your desired outcome.

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