formula help

i need help with a formula please. my current formula looks like this..

=SUMIF(Stage:Stage, ???, [Job Value]:[Job Value])

i need an answer to the ??? please as it is a column that has a formula in it that relates to other fields and is a check box. if true then tick the check box.

i am now wanting to sum the value of all of the checked box rows in my sheet.

please help

Find more posts tagged with

Manufacturing

Formulas

Comments

Paul Newcome

For checkboxes, flags, and a few other symbols that only have 2 options, 1 = Checked and 0 = Unchecked.

BlairMyles

thanks Paul, thats what i have tried. below is the formula as i had it written but it wont work

=SUMIF(Stage:Stage, 1, [Job Value]:[Job Value])

the Stage Column has its own formula in it which is an IF statement so i wonder if this is whats causing the above formula not to work???

Paul Newcome

I just tested it, and it worked fine for me. Even with a formula driving the checkbox. Are there any formulas being used in the column you are trying to add? If so, you will want to wrap them in a VALUE function.

=VALUE(Formula))

That will give you the actual number value as opposed to the string of text that represents the number in the background of Smartsheet.

J. Craig Williams

For check boxes, you don't want SUM, you want COUNT. But that isn't quite what you are doing, it appears.

This formula

=SUMIF(Stage:Stage, 1, [Job Value]:[Job Value])

is summing the column of [Job Value] when the match [Stage] is checked.

Is that what you are trying to do?

Where is the formula located? If it is in the [Stage] column, that's your error. Check box columns can only have Boolean (True/False or 1/0) or Text values.

"23" (Text) is OK. 23 (a number) is an error.

If you really want to have a number displayed in Check Box column, convert it to a Text like so

=SUMIF(Stage:Stage, 1, [Job Value]:[Job Value]) + ""

and then if you need it as a number somewhere else, use the VALUE() function that Paul mentioned.

Craig

BlairMyles

ok so slight variation on the earlier question but using the same premise

here is my formula that returns a number in a set column

=MONTH([Cust Requested Date]119)

answer to above formula =8

ie current month

Here is the formula that i am trying to insert the answer to the above formula into

=SUMIF([Revenue Next 30 Days Cumulative]:[Revenue Next 30 Days Cumulative]) =MONTH3, [Job Value]:[Job Value])

in the above formula =MONTH3 provides the answer 8 from an adjoining column and this 8 is a manual input

can you please help me construct the formula correctly. where do i add the =VALUE statement?

is it to the top formula or embedded in the bottom one...

and if so how should it look.

thanks heaps

blair

BlairMyles

ok so slight variation on the earlier question but using the same premise

here is my formula that returns a number in a set column

=MONTH([Cust Requested Date]119)

answer to above formula =8

ie current month

Here is the formula that i am trying to insert the answer to the above formula into

=SUMIF([Revenue Next 30 Days Cumulative]:[Revenue Next 30 Days Cumulative]) =MONTH3, [Job Value]:[Job Value])

in the above formula =MONTH3 provides the answer 8 from an adjoining column and this 8 is a manual input

can you please help me construct the formula correctly. where do i add the =VALUE statement?

is it to the top formula or embedded in the bottom one...

and if so how should it look.

thanks heaps

blair

Paul Newcome

=SUMIF([Revenue Next 30 Days Cumulative]:[Revenue Next 30 Days Cumulative]), = MONTH([Cust Requested Date]119), [Job Value]:[Job Value])

This should work for you...

BlairMyles

i have pasted this exact formula in and it comes back unparsable sorry.

any other suggestions???

Regards

Blair

Paul Newcome

What column type are you putting your formula in?

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…