Stuck on IF, AND, and SUM formula

Abby Overstreet

I am trying to create a semi-complex formula. I have two check boxes. When someone clicks check box "A", it sums up specific columns. When someone clicks check box "B", it sums up the other columns. When someone clicks both "A" and "B" check boxes, it'll sum up all of the columns.

I am starting with this (I've edited the column slightly to use more neutral terms):

=IF(AND([Box A]@row = "true", [Box B]@row = "true"), =SUM(Column3:Column11), "false")

I am able to get the individual parts working, but I am getting an #invalidoperations when putting it all together.

If I replace the sum function with ' "true" ' or something simple, the sheet is able to read when I have both columns clicked, and therefore will spit out "true." But when the formula is in place, it says #Invalidoperations.

Could someone help explain why this might be the case? I've already double checked to make sure all the columns are reading as numbers and not text.

When I use =SUM(Column3:Column11) standalone, I get the proper sum.

Lastly, when I try to build the entire logical expression, the last parts of the equation work, while the first

=IF(AND([Column A]@row = "true", [Column B]@row = "true"), =SUM(Column3:Column11), IF([ColumnA]@row = "true", SUM(Column3, Column4, Column5, Column6, Column11), IF([Column B]@row = "true", SUM(Column7, Column8, Column9, Column10, Column11), " ")))

So when clicking either column "A" or column "B" by itself, I am able to get the proper calculation for those respective check boxes. It's just the combined formula I am struggling with.

Thank you!

Neil Watson

Hi @Abby Overstreet

In the first condition, your columns are in the correct format: Column:Column.

The 2nd and 3rd condition, try

=IF(AND([Column A]@row = "true", [Column B]@row = "true"), =SUM(Column3:Column11), IF([ColumnA]@row = "true", SUM(Column3:Column3, Column4:Colum4, Column5:Column5, Column6:Column6, Column11:Column11), IF([Column B]@row = "true", SUM(Column7:Column7, Column8:Column8, Column9:Column9, Column10:Column10, Column11:Column11), " ")))