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?

Question

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?

Answer 1

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)

Answer 2

I went with this in the end:

=IF(B14<>"",IF(C14<>"",IF(D14<>"",IF(E14<>"",IF(F14<>"", SUM(B14:H14),""),""),""),""),"")

Note that my sheet includes Saturday and Sunday which I wanted to include in the SUM() but not in the IF() statement

Answer 3

Below is an array formula that will handle the entire "Total" column. Just delete everything from Column G (which I assume is your "Total" column), including the header, and place this where "Total" used to be:

=ArrayFormula({"TOTAL"; IF(MMULT(IF(B2:F="",0,1),SEQUENCE(5,1,1,0))<5,,MMULT(IF(B2:F="",0,B2:F),SEQUENCE(5,1,1,0)))})

HOW IT WORKS:

Obviously, this is an array formula, which means it will process and/or output a full range of data. No drag-down necessary.

The key here is MMULT (or matrix multiplication). This function seems to perpetually mystify people. So rather than explain it in words, I'll just show you a graphic I made that shows what it does visually:

Every element in both matrices must be a number. Since you've got blanks, we'll need to convert those to zero first. Hence IF(B2:F="",0,1). This means (in plain English), "For any element in the range B2:F, if it's a blank, make it a zero; and if not, make it a 1." This is then multiplied by the second part of the first MMULT: a SEQUENCE of 5 rows (to match the number of columns to be processed), 1 column, starting at 1 and skipping by 0. In other words, it will form a virtual column of five instances of 1 on top of one another. Since anything times 1 is itself, the result will be a row-by-row COUNT of how many elements are in each row of B2:F.

If that number is less than 5, do nothing. (That's the comma-comma with nothing between.)

If that number is exactly 5 then keep going to the second MMULT.

The second MMULT is the same as the first, except that instead of replacing numbers with 1, it replaces them with themselves. When multiplied by the column of 1's, we get a row-by-row SUM of each row in the range B2:F.