Form Help

I've created a form and introduced logic for several responses. If the field is populated, a follow up question with radio buttons becomes visible, 'A' and 'B'. I want to be able to calculate at the sheet level the total of all questions answered 'A' and also calculate all answers calculated 'B'.

I assume an IF statement would be appropriate, but I've played around with it and have been unsuccessful.

Thanks for your help!

Nathan

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Nathan Umbriac

    I hope you're well and safe!

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots and the formula(s) you're trying to get workting? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Nathan Umbriac

    It sounds like you're looking to use a COUNTIF statement to COUNT how many rows match your criteria, is that the end goal?

    If so, try something like this:

    =COUNTIF([Column Name]:[Column Name], "A")

    This will count how many times A is selected in the column "Column Name". You can put this formula in a Sheet Summary Field in your sheet (depending on your plan type).

    Here are some Help Center resources that may be useful as you build out your formulas and metrics:


    If I've misunderstood what you're looking to do, I agree with Andrée: it would be helpful to see a screen capture of your source sheet set up, identifying what it is you're looking to achieve, but please block out any sensitive data.

    Cheers!

    Genevieve

  • Nathan Umbriac
    Nathan Umbriac ✭✭✭✭✭

    @Andrée Starå and @Genevieve P Thank you both.

    I included a single select radio button on my form. Once the field is populated with a value, logic displays a follow up question with options 'A' or 'B'.

    I'm trying to sum the values of all A selections and all B selections, separately. For all A selections, the total sum is X and for all B selections, the total sum is Y.

    Thanks again for your guidance!

    Nathan

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Nathan Umbriac

    Did you try implementing the COUNTIF statement above? That will count how many rows have option "A" selected and provide you with a number. Then you can copy/paste this same formula and swap out "A" for "B" to find the count of rows that have "B".

    If the formula isn't working for you, or if you are unsure how to adjust it to match your current column values/column title, please provide us with a screen capture and we'll be happy to help further.

  • Nathan Umbriac
    Nathan Umbriac ✭✭✭✭✭

    Thanks @Genevieve P. The COUNTIF statement works, but what I need is the total value of the associated fields for 'A' and 'B'.

    If any activity has 150 units, for example, and 'A' is selected, I want that and all other 'A' selections to add up. Same for 'B'.

    Hope this clarifies, and thanks again for your help!

    Nathan

  • Genevieve P.
    Genevieve P. Employee Admin

    Ah! Thank you for clarifying. You're looking for a SUMIF formula then, versus COUNTIF.


    A SUMIF works like this:

    =SUMIF([Criteria Column]:[Criteria Column], "Criteria", [SUM Column]:[SUM Column])


    So in your case, for A:

    =SUMIF([A/B Column]:[A/B Column], "A", [Units Column]:[Units Column])


    Here's the documentation for the SUMIF function.

    Cheers,

    Genevieve

  • Nathan Umbriac
    Nathan Umbriac ✭✭✭✭✭

    Thanks @Genevieve P.

    I am using the following formula, and have moved all referenced columns in my sheet next to one another:

    =SUMIF([Product Type for Activity 1]@row:[Product Type for Activity 20]@row, "Product A", [Activity X]@row:Activity Y@row)

    I am getting #INCORRECT ARGUMENT

    Thanks for your guidance!

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Nathan Umbriac

    Seeing what you have tried helps a lot, as it identifies that you are looking across rows and multiple columns instead of just one column which will change how you should build your formula.

    In looking at what you've tried, I'm guessing you have 20 columns identifying the activity, and each column could either be A or B, is that right? Can you clarify what it is you're looking to SUM, then, depending on what's selected? (Are there 20 Activity columns, each corresponding to a Product column?)

    It's difficult to help further without seeing a screen capture of your sheet set-up; could you potentially create a duplicate of the sheet and delete out sensitive data, then post a screen capture of this example sheet?

    Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!