Check boxes that reference numbers and adding those numbers. Please help!

Joe Foster

I have two sheets one with software costs and another that has check boxes. I need the checkboxes to reference the costs then add together for a final number basically. I'm very new to Smartsheet as a whole and don't really know how to do it.

Joe Foster

I figured it out the line I used was

=IF([BlueBeam Revu]@row, {Software License Ref Sheet Range 1}, 0) + IF([Microsoft Office]@row, {Software License Ref Sheet Range 4}, 0) + IF([Microsoft Office (Mac)]@row, {Software License Ref Sheet Range 5}, 0) + IF([Microsoft Project]@row, {Software License Ref Sheet Range 6}, 0) + IF([Microsoft Visio]@row, {Software License Ref Sheet Range 7}, 0) That seems to easily get me the result I wanted....Wow why couldn't i figure that out before.

MCorbin

Great! Glad to hear it. Sorry my suggestion was confusing - yours is actually better :-)

MCorbin

I think you need a little more information to help you come up with your formula

The first sheet just has checkmarks -

I'm assuming that if there is a checkmark, you want to lookup the cost per year in the 2nd sheet and add the costs for any products that are checked, right?

You don't have column names in your 2nd screenshot, so I created a sheet - my formula will use these column names:

I would recommend in your first sheet, creating a Helper Row - it will significantly cut down on the work for your formula. Copy each product name, exactly the same as you have it in the Product sheet, into this helper row:

Then you could create a formula that, if the box in that column is checked, looks up the cost in the price sheet and then adds all those together. Note: If you have a lot of products, this could be a really long formula:

=IF([Adobe DC]@row = 1, INDEX({Cost}, MATCH([Adobe DC]$1, {product}, 0)), 0) + IF([Adobe Creative Cloud]@row = 1, INDEX({Cost}, MATCH([Adobe Creative Cloud]$1, {product}, 0)), 0) + IF([BlueBream Revu]@row = 1, INDEX({Cost}, MATCH([BlueBream Revu]$1, {product}, 0)), 0) + IF(Egnyte@row = 1, INDEX({Cost}, MATCH(Egnyte$1, {product}, 0)), 0)

Joe Foster

https://community.smartsheet.com/discussion/comment/279387#Comment_279387

Why is there a product line though? Does it reference the helper line? If so, then why use the {} and not the []? Also I can't get that code you to work as I'm not sure where stuff goes. I'm sorry I'm not great at this. Here's some more screen shots if they will help.

column5 and 6 are just place holders.

Joe Foster

Could you possibly send me that sheet so I may look at it. I'm not understanding the line you wrote or how to make it work.

Joe Foster

I figured it out the line I used was

=IF([BlueBeam Revu]@row, {Software License Ref Sheet Range 1}, 0) + IF([Microsoft Office]@row, {Software License Ref Sheet Range 4}, 0) + IF([Microsoft Office (Mac)]@row, {Software License Ref Sheet Range 5}, 0) + IF([Microsoft Project]@row, {Software License Ref Sheet Range 6}, 0) + IF([Microsoft Visio]@row, {Software License Ref Sheet Range 7}, 0) That seems to easily get me the result I wanted....Wow why couldn't i figure that out before.

MCorbin

Great! Glad to hear it. Sorry my suggestion was confusing - yours is actually better :-)