Total: Checkbox and Other Criteria

Graham Cracker

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

Nick Korna

@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

@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

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.

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

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

@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

Wow. That worked perfectly.

Thank-you so much @Nick Korna!

Nick Korna

No problem, happy to help! :)

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

@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

@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

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

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

Nick Korna

No problem, glad to have helped!