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

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

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

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

    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!