Google-sheets – In Google Sheets or Excel, how can I write a formula to SUM() a range based on the criteria that none of the respective cells are blank

google sheetsmicrosoft excel

What I want to do:

+------+--------+---------+-----------+----------+--------+-------+------------+
| Week | Monday | Tuesday | Wednesday | Thursday | Friday | Total | Difference |
+------+--------+---------+-----------+----------+--------+-------+------------+
| 1    | 8      | 8       | 8         | 9        | 8      | 41    | 1          |
+------+--------+---------+-----------+----------+--------+-------+------------+
| 2    | 8      | 8       | 8         | 8        | 8      | 40    | 0          |
+------+--------+---------+-----------+----------+--------+-------+------------+
| 3    | 8      | 8       |           |          |        |       |            |
+------+--------+---------+-----------+----------+--------+-------+------------+

In the above table, I want a formula in the 'total' column that will only sum the values from Monday to Friday when all five cells contain a value. If any of the cells are empty then it shouldn't sum them.
How can I do this?

Best Answer

Here's a shorter option:

=IF(COUNTA(B2:F2) < 5, "", SUM(B2:F2))

The 5 is the number of values you are looking for. (The number of days of the week you have listed)

Related Topic