0

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?

3 Answers 3

2

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)

1
0

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

0

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:

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.