Sheet Summary Question
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.
Best Answers
-
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, 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...
- Delete your original Sheet Summary field
- Create a new one
- 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.
Answers
-
I think you need to add an equal sign to your formula.
=COUNTIF([Active Dealers]:[Active Dealers], ="Active")
-
That is still returning a #NO MATCH
-
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 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.
-
Please disregard I figured it out
-
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?
-
@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...
- Delete your original Sheet Summary field
- Create a new one
- 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.
-
Thank you for all of the help @ccarriger
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!