Add Numeric Fields together if a checkbox is checked

Hi, I'm building an app where the project is projecting out 5 years, but the project owner might only ask for funding for 1 or 2 years (to keep costs manageable, but they need to project to 5 years).

I made a multi-choice field to select which years is the "current ask".
Year 1, Year 2, Year 3, Year 4, Year 5 (these are formula checkbox)

Then that field is a lookup on the table "resources" where all of the details/math/etc are done.

I have checkboxes on the resources table that says
Year 1 ask
year 2 ask, and so on.

I want to calculate the financials for the "current ask". So If Year 2, and Year 3 are the "current ask", then take Year 2 Financials, and Year 3 financials and add them together to give me a total for those years.

Here's the formula I've come up with:

var text checkbox = (ToText([Year 1 Ask] or [Year 2 Ask] or [Year 3 Ask] or [Year 4 ask]));

If($checkbox = "true",

Nz([YEAR 1]) + Nz([YEAR 2]) + Nz([YEAR 3]) + Nz([YEAR 4]) + Nz([YEAR 5]))

I'm getting a syntax error every which way I turn. What is the correct way to write this?

formulas

MarkShnier__You
6 months ago
Try this
IF([Year 1 Ask], [YEAR 1],0)
+
IF([Year 2 Ask], [YEAR 2],0)
+
IF([Year 3 Ask], [YEAR 3],0)
+
IF([Year 4 Ask], [YEAR 4],0)
+
IF([Year 5 Ask], [YEAR 5],0)

MarkShnier__You
Qrew Legend
6 months ago
Try this
IF([Year 1 Ask], [YEAR 1],0)
+
IF([Year 2 Ask], [YEAR 2],0)
+
IF([Year 3 Ask], [YEAR 3],0)
+
IF([Year 4 Ask], [YEAR 4],0)
+
IF([Year 5 Ask], [YEAR 5],0)
- ReneeHansen1
  Qrew Cadet
  6 months ago
  Thank you! Thank worked. And for my learning purposes, is that the best way to handle these types of scenarios or are there other options for getting this result as well?
MarkShnier__You
Qrew Legend
6 months ago
Well, just by way of education, an alternative syntax would be this for each element

Nz(IF([Year 1 Ask], [YEAR 1])
The Nz function will return zero instead of null if the result is nothing. Unlike Excel, QuickBase will differentiate between a null result and a zero . Excel tends to treat blanks at zeros but when Quickbase runs up against a null it causes the result of the calculation to be null. More or less it says well if you don't know what this value is then if I use it in an expression of something which is unknown plus 1 the result is something unknown.
So that is why in the original formula I posted I basically said hey if the checkboxes checked use this value else use zero, that was to avoid the result of that piece of the formula calculating to null

A comment about formulas in Quickbase is this. In many other programming languages fancy programmers try to do the absolute most amount of calculations with the least amount of code regardless if the code is completely cryptic. Excel can be that way with a complicated nested IF statement.
But in QuickBase, the goal should always to wright formulas in a way that are completely stupid simple to understand. Either future you, two years for now will look back at this formula and have to make a change to it to try to figure out what it's doing, or your successor will inherit your application and look at this formula and have to try to figure it out.

The other great tool for writing formulas, which are stupid simple to understand is a concept of formula variables.
https://helpv2.quickbase.com/hc/en-us/articles/4570254813332-Formula-variables

Forum Discussion

Add Numeric Fields together if a checkbox is checked