formula help

BlairMyles

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

Paul Newcome

1

 

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?