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

• ✭✭✭✭✭✭

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

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

• ✭✭✭✭✭✭

Amend your formula along the lines of:

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

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭

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

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:

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!

• ✭✭✭✭

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.

• ✭✭✭✭✭✭

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

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

• ✭✭✭✭

Wow. That worked perfectly.

Thank-you so much @Nick Korna!

• ✭✭✭✭✭✭

No problem, happy to help! :)

• ✭✭✭✭

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

• ✭✭✭✭✭✭

Amend your formula along the lines of:

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

• ✭✭✭✭

@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 :/

• ✭✭✭✭✭✭

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.

• ✭✭✭✭

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

• ✭✭✭✭✭✭

No problem, glad to have helped!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!