Sheet Summary Question

ConnorForm

Hello all, I am trying to do a sheet summary to show the Active Dealers vs Non Active Dealers that are running campaigns. An active dealer will have a monthly total greater than 0. However the Monthly Total Column in the smartsheet is a referencing information from another smartsheet using a Vlookup. I am trying to do a CountIF to count the amount of active dealers based off their monthly total being greater than zero however the formula is showing #NO MATCH and I think this is because of the cell reference / vlookup formula. I tried making a seperate column with a formula IF([Monthly Total]@row greater than 0, "Active", "Not Active" and do a Count If off of that column however I am still getting the #NO MATCH error.

ccarriger

Can you create a checkbox column and use an automation to check it if Active Dealers = Active? Then run your formula off of that as =COUNTIF([Active Dealers Checkbox]:[Active Dealers Checkbox], ="true"). You can totally hide the column but still have it available to run your sheet summary field formula.

ccarriger

@ConnorForm, I just tested it and it should work on a formula field like your Active Dealers one. Screenshot below.

I'm not sure why it's not working in your Sheet Summary though, and really the fact that you have the checkbox column running on a formula and being referenced by a formula also confirms that.

My recommendation...

1. Delete your original Sheet Summary field
2. Create a new one
3. Type in the Active Dealers formula fresh (not the checkbox just the original Active one you referenced)

Sometimes it helps to try it again--I've seen that fix issues after someone checked my formula here. Just random glitches or whatever.

ccarriger

I think you need to add an equal sign to your formula.

=COUNTIF([Active Dealers]:[Active Dealers], ="Active")

ConnorForm

That is still returning a #NO MATCH

ccarriger

Can you create a checkbox column and use an automation to check it if Active Dealers = Active? Then run your formula off of that as =COUNTIF([Active Dealers Checkbox]:[Active Dealers Checkbox], ="true"). You can totally hide the column but still have it available to run your sheet summary field formula.

ConnorForm

@ccarriger Could you please provide the formula you would use for the Checkbox Column to get it to automatically check. The Active Column formula currently: =IF([Monthly Total]@row > 0, "Active", "Not Active")

Just to understand correctly you are saying to create another column that is checkboxes to look at whether the Active Column reads "Active or Not Active" and to check a box if it reads active

Would it start/look something like this for the Checkbox Active Column?

=IF([Active]@row = "Active", =True)

Thanks for all your assistance and sorry for the inconvenience.

ConnorForm

Please disregard I figured it out

ConnorForm

The Sheet Summary works on the Active Check columns for the Count IF formula. To conclude, I do not know if I can get some confirmation here, but can you not do Sheet Summaries on columns that are cell references from other sheets?

ccarriger

@ConnorForm, I just tested it and it should work on a formula field like your Active Dealers one. Screenshot below.

I'm not sure why it's not working in your Sheet Summary though, and really the fact that you have the checkbox column running on a formula and being referenced by a formula also confirms that.

My recommendation...

1. Delete your original Sheet Summary field
2. Create a new one
3. Type in the Active Dealers formula fresh (not the checkbox just the original Active one you referenced)

Sometimes it helps to try it again--I've seen that fix issues after someone checked my formula here. Just random glitches or whatever.

ConnorForm

Thank you for all of the help @ccarriger