Sum or count only if there is a certain character in another cell

Question

I have a Google spreadsheet, where Column A will contain either a "1" or "X" character. In column B, C, and D (same row), I have a quantity value (i.e. "1" or "2" etc). What I'm trying to do is SUM the values in B, C, and D ONLY if column A = 1. If A = X, just 0 or null returned. Is something like that possible?

Answer 1

There are a couple of ways you could do this, but if you wanted to apply it to the entire column, you would want to use arrayformula() around the entire thing. In order to do that you are limited. I like to place the arrayformula() in the header row so it is protected. For your needs, it would look something like this:

=ARRAYFORMULA(if(ROW(A1:A) = 1, "Sum Row", IF(A1:A = 1, B1:B + C1:C + D1:D, )))

The first thing this does is use an IF() which checks to see if it is working on the first row. If it is, it places a piece of text there. "Sum Row" in this case, without the quotes, of course.

For any other row, that IF() results in false so it does the second half, or FALSE portion. This is another IF() which checks the value of that row's column A. If the value is 1 it then does the TRUE portion which is to add that row's columns B through D. Wee cannot use SUM() here because that would sum all the rows. The FALSE portion of this IF() returns nothing.

If you want to have a value when there is something in column A and no value when there is not, you can wrap another IF in there. using ISBLANK() to see if there is a value in column A:

=ARRAYFORMULA(if(ROW(A1:A) = 1, "Sum Row", IF(A1:A = 1, B1:B + C1:C + D1:D, IF(ISBLANK(A1:A), , 0))))

Added based on OP Comment:

If you do not label your columns in row 1, you can place the following in the top cell where you want to start showing the sum of the row when that row's cell has a value of 1:

=ARRAYFORMULA(IF(A1:A = 1, B1:B + C1:C + D1:D, ))

Assuming the ARRAYFORMULA() is placed in a cell in row 1 it looks at the references such as A1:A and replaces this with A(rownumber) so that for row 9 all references in the above formula are changed to A9, B9, C9, and D9. In row 9 the formula looks at cell A9 and if it is 1 it then adds B9, c9, and D9 to give you their sum and places it in row 9 where the formula is placed So if the formula is in cell G1 it will place the answer for row 9 in cell G9. If A9 is anything but 1, this will place nothing in cell G9. If you want a 0 in instead of nothing when cell A in a given row has a value other than one, then add the ISBLANK() as below:

=ARRAYFORMULA(IF(A1:A = 1, B1:B + C1:C + D1:D, IF(ISBLANK(A1:A), , 0)))

In our above example for row 9 this will give you a 0 if cell A9 has a value that is not 1 and will not put anything in the cell if A9 is blank, meaning it has no value. Karl

Answer 2

=ARRAYFORMULA(IF(A2:A=1, B2:B+C2:C+D2:D, ))