Sumif formula with checkbox reference

Hollie205

Can someone provide a second look and explain why the formula isn't working the way I believe it should based on formula logic.

I'm having an issue with a sumifs adding my dep amt when the cleared check box is checked. Below is my formula and my input and out put sheet. It adds it when it is unchecked. If I change my formula to "0" for the cleared check box dep amt sums correctly but not for amt pd. It's not making sense to me. Normally I would just do the work around but if it is not working the way it should and suddenly starts working the way it should my balance will be incorrect and could cause overdrafts.

Formula

=[Remaining Balance]@row + SUMIFS({Deposit}, {Pd From}, [Primary Column]@row, {cleared}, "1") - SUMIFS({Amt Pd}, {Pd From}, [Primary Column]@row, {cleared}, "1")

Data input sheet

Data output results

Eric Law

@Hollie205 Remove the parenthesis around the 1, so it's not looking for a string that has the 1 icon and it is looking for a true/false. Hope that fixes it!

Hollie205

Gives the same results

Kelly Moore

Hey @Hollie205

Are you sure that your Amt Paid column is registering to smartsheet as a number value, and not a text string. I notice the column appears as left justified- which typically indicates a text string. A SUMIFS could not sum the column if smartsheet thought the column contained text.

As a quick test, you can try =SUM([Amt Pd]:[Amt Pd]) . If the sum is zero then the data isn't registering as numeric. If you want to test a specific value, you can do an IF(ISNUMBER([Amt Pd]@row), "true", "false")

Kelly

Hollie205

Thanks ! @Kelly Moore It wasn't the resolution but caused me to try a few things. I've never had anything like it.

Resolution: Removed Account Nicknames from the Primary Column. It didn't work to just create a new column and reference it. I had to actually delete them from the Primary Column.

Prior to that I also moved the numbers out of the Primary Column on my other sheet labeled Amt Pd. Just doing that did not fix the issue. Not sure if that was required for the actual resolution to work but wanted to share in case someone else ran across the same issue.

Kelly Moore

Hey @Hollie205

Glad you found the fix. As @Eric Law pointed out above, and you probably know, be mindful that quotes around numbers will convert the data into a textstring number character- it looks like a number to us so it's hard to catch that smartsheet isn't treating it as a number value. So no quotes around numbers in formulas unless you are intentionally wanting the value to behave as text. If a text parsing function is used (ie LEFT, MID, SUBSTITUTE, etc) to extract the data, often you must wrap that formula in a VALUE() function to convert the formula output to a number. Otherwise it is again that textstring that looks like a number.

Kelly