This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hi,
I don't know why Excel returns the wrong answer when I'm using this formula:
{=COUNT(IF((C2:C21=C10)+((A2:A21=A3)*(B2:B21=B6)),F2:F21,""))}
The answer should be 6, but it's 7
Processes in Microsoft 365 for setting up Office apps, redeeming product keys, and activating licenses.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(12.8, 18%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
Hi,
{=SUM(IF((C2:C21=C10)((A2:A21=A3)(B2:B21=B6)),1,0)*IF(F2:F21<>"",1,0))}
Try using this formula once.
Remember to enter this formula as an array formula by pressing Ctrl+Shift+Enter after typing the formula.
Best Regards.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 53%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
The first condition in your if is nonzero 6 times: rows 10-12, 16, 19, and 21.
The second condition is nonzero 3 times: rows 3, 13, and 20. The third condition is nonzero 4 times: rows 6, 10, 17, and 20. The product of these two conditions is nonzero once: row 20.
The sum of these expressions is nonzero 7 times: rows 10-12, 16, 19, 20, and 21.
Seven appears to be the correct answer for what you wrote. We might be able to help with a better formula if you described what you are trying to count.
I asked Excel count of "SE" in the Region column, it must return 6, not 7
That may be what you meant to ask for but your code asks for that PLUS some additional matches. If all you want is SE matches, what is the purpose of the product expression after the plus sign.
Because I am testing "and" and "or" conditions
In your formula, the + is the equivalent of OR while your product expression is equivalent to AND. OR is inclusive while AND is restrictive. The result is a count of things that matched SE or matched Loman/Dryer. Six rows satisfy the first match and one ADDITIONAL row satisfies the second. If you swapped the regions in rows 19 and 20, there would still be seven matches but now two of the matches would be on the same row and the answer would be six.
Since the formula you posted does not do what you want, why not tell us what that is so we can help you generate a formula that does.