formula help

BlairMyles
edited 12/09/19 in Formulas and Functions

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

Comments

  • Paul Newcome
    Paul Newcome Community Champion

    1

     

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

  • 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
    Paul Newcome Community Champion

    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
    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

     

  • 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

  • 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
    Paul Newcome Community Champion

    =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...

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

     

    any other suggestions???

     

    Regards

     

    Blair

  • Paul Newcome
    Paul Newcome Community Champion

    What column type are you putting your formula in?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!