Count non-empty cells that fulfill a criterion

Question

Here is something that should be basic, but I cant' figure it out.

So, I have information in columns A:G. Some of the cells in columns B:G are empty, others not.

In the following spreadsheet, how do I find out how many cells in the area B:G are non-empty provided the criterion "John" is filled in column A:A?

My try, which does not work:
=COUNTIFS(A:A, "John", B:G, "*")

+----+-------+---------+---------+---------+-------+-----------+--------+
|    |   A   |    B    |    C    |    D    |   E   |     F     |   G    |
+----+-------+---------+---------+---------+-------+-----------+--------+
|  1 | John  | bla     | blaba   | fsdfs   |       | sdfsdfdsf | sdfsdf |
|  2 | Bill  | sfsdf   |         | sdfsdf  |       | sdfsdsd   |        |
|  3 | Tim   |         |         |         |       |           |        |
|  4 | John  | sdfsdf  | ssdfsdf |         |       | sdfsdf    | sdfsd  |
|  5 | Rick  | egerreg | g       | gergerg |       | g         |        |
|  6 | Steve |         |         |         | ergrg |           |        |
|  7 | Rick  | ergerg  | er      | gergerg |       |           |        |
|  8 | John  |         | reger   |         |       |           | erge   |
|  9 | Tim   |         | erg     |         | erg   |           |        |
| 10 | Rick  | ergerg  |         |         |       | ergerg    | erge   |
+----+-------+---------+---------+---------+-------+-----------+--------+

Answer 1

You're right. It is fairly basic. You can do it with:

=SUMPRODUCT((A1:A10="John")*(NOT(ISBLANK((B1:G10)))))

or

{=SUM((A1:A10="John")*(NOT(ISBLANK((B1:G10)))))}

The second formula is array-entered, of course.

Note that while the formula would still work if you used entire columns, this is not recommended as the formula will take a long time to calculate.

Answer 2

According to Microsoft Office help center regarding COUNTIFS:

Important: Each additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other.

Which is the reason why your COUNTIFS fails: the argument B:G doesn't have the same number of columns as the first argument, A:A.

---

One way to achieve this would be to use a SUMPRODUCT, which doesn't have this size limitation.

You can use, as RobinCTS suggested, the following formula:

=SUMPRODUCT((A1:A6="John")*(NOT(ISBLANK(B1:E6))))

However, if a cell has a 0 inside, it won't be considered empty.

---

An alternative solution to Robin's answer, is to use the fact that both an empty cell and a cell that contains a 0 will return TRUE when tested to these conditions: <>0 and <>"".

=SUMPRODUCT((A1:A6="John")*(B1:E6<>0))
=SUMPRODUCT((A1:A6="John")*(B1:E6<>""))

Depending on what you need, and the kind of data contained in your workbook, one of these 2 solutions should work.

This is illustrated in the example below: