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

A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?
SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…