Total: Checkbox and Other Criteria

Hi,

Need help with a formula that will give me a number based on whether a box has been checked in one column (Survey Complete) + the name of the Prior Business (there are 4 different types) in another column.

I'm guessing I will need 4 different formulas to total the number of checked boxes for each prior business name. We can call the businesses Wal-Mart, Target, Lowes and Home Depot.

Thanks for your help!!

Best Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“

    @Graham Cracker: if the prior business is blank you can use the previous formula but instead of having anything inside the "" leave it empty:

    =COUNTIFS([Prior Business]18:[Prior Business]395, "", [Survey Complete]18:[Survey Complete]395, 1)

    Should count all the checked boxes with nothing in the prior company on rows 18 to 395.

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“

    @Graham Cracker,

    Amend your formula along the lines of:

    =COUNTIFS([Prior Business]:[Prior Business], "Wal-Mart", [Survey Complete]:[Survey Complete], <>"")

    The 2nd part of the COUNTIFS is now checking that there is any value in there to be counted.

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    edited 02/23/23 Answer βœ“

    HI @Graham Cracker,

    Your formula is almost right, just as you only have 1 criteria you can SUMIF instead (for a single letter change!):

    =SUMIF([Prior Business]52:[Prior Business]334, "Wal-Mart",[Interior Signage Quote: $3,500 Budget/Site]52:[Interior Signage Quote: $3,500 Budget/Site]334)

    For SUMIFS, the order is reversed, so for a single criteria would be:

    =SUMIFS([Interior Signage Quote: $3,500 Budget/Site]52:[Interior Signage Quote: $3,500 Budget/Site]334,[Prior Business]52:[Prior Business]334, "Wal-Mart")

    If you had other criteria to add, the ranges/criteria would be added onto the 2nd formula. If just 1, it's your entirely your choice which formula to use! 😊

    This is assuming you're only wanting to check rows 52-334.

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @Graham Cracker ,

    If you're doing it on the same sheet then you can use the formula below:

    =COUNTIFS([Prior Business]:[Prior Business], "Wal-Mart", [Survey Complete]:[Survey Complete], 1)

    Obviously changing the business name for each. The example above is in the sheet summary, but you can do this elsewhere in the sheet, or you could have the summary in another sheet and use cross-sheet references instead:

    Here the formula in the 2nd column is:

    =COUNTIFS({Checkbox + Business Range 1}, Business@row, {Checkbox + Business Range 2}, 1)

    With the 2 cross sheet references (indicated by the { } brackets being:

    This option would make it easier if you then need to add a 5th+ option to the summary list.

    Hopefully this is helpful, but if you've any questions then just ask!

  • Graham Cracker
    Graham Cracker ✭✭✭

    This is amazing!! Thank-you @Nick Korna. What about if the prior business is blank? What would that formula be? I tried this but it didn't work.

    =COUNTIFS([Prior Business]18:[Prior Business]395, ISBLANK, [Survey Completed]18:[Survey Completed]395, 1)

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“

    @Graham Cracker: if the prior business is blank you can use the previous formula but instead of having anything inside the "" leave it empty:

    =COUNTIFS([Prior Business]18:[Prior Business]395, "", [Survey Complete]18:[Survey Complete]395, 1)

    Should count all the checked boxes with nothing in the prior company on rows 18 to 395.

  • Graham Cracker
    Graham Cracker ✭✭✭

    Wow. That worked perfectly.

    Thank-you so much @Nick Korna!

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    No problem, happy to help! :)

  • Graham Cracker
    Graham Cracker ✭✭✭

    @Nick Korna Let's say "survey complete" was at text column instead of check box. And for each prior business (Wal-Mart, Target, Lowe's, Home Depot), if there is anything written in the corresponding row for the column "Survey Complete" it is worth 1 and I can add those cells up.

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“

    @Graham Cracker,

    Amend your formula along the lines of:

    =COUNTIFS([Prior Business]:[Prior Business], "Wal-Mart", [Survey Complete]:[Survey Complete], <>"")

    The 2nd part of the COUNTIFS is now checking that there is any value in there to be counted.

  • Graham Cracker
    Graham Cracker ✭✭✭

    @Nick Korna how do I configure the formula if I want it to add all cells that are in rows designated "Wal-Mart" only. This is my guess: =SUMIFS([Prior Business]52:[Prior Business]334, "Wal-Mart", [Interior Signage Quote: $3,500 Budget/Site]52:[Interior Signage Quote: $3,500 Budget/Site]334)

    ...but it's not working :/

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    edited 02/23/23 Answer βœ“

    HI @Graham Cracker,

    Your formula is almost right, just as you only have 1 criteria you can SUMIF instead (for a single letter change!):

    =SUMIF([Prior Business]52:[Prior Business]334, "Wal-Mart",[Interior Signage Quote: $3,500 Budget/Site]52:[Interior Signage Quote: $3,500 Budget/Site]334)

    For SUMIFS, the order is reversed, so for a single criteria would be:

    =SUMIFS([Interior Signage Quote: $3,500 Budget/Site]52:[Interior Signage Quote: $3,500 Budget/Site]334,[Prior Business]52:[Prior Business]334, "Wal-Mart")

    If you had other criteria to add, the ranges/criteria would be added onto the 2nd formula. If just 1, it's your entirely your choice which formula to use! 😊

    This is assuming you're only wanting to check rows 52-334.

  • Graham Cracker
    Graham Cracker ✭✭✭

    Thank-you @Nick Korna!! That did it :)

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    No problem, glad to have helped!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!