Can I use the Countif function to count the amount of times one column is greater than another?
For example, in my Smartsheet I have two columns that I would like to compare data. "Sprinkler Head Count (Estimated)" versus "Sprinkler Head Count (Actual)."
I would like to count how many times in my sheet where the Actual head count is greater than the Estimated head count.
Any ideas how we can achieve this?
Thanks in advance!
Best Answers
-
create a checkbox column with this formula:
=if(Sprinkler Head Count (Actual) >Sprinkler Head Count (Estimated),1,0)
Then you can do a countif statement counting the checkboxes: =countif([checkbox column]:[checkbox column],1)
-
I'm not sure if it can be done with a single formula. Here's what I would do.
Create a helper column that's a checkbox column called something like "Sprinkler (helper)". Write this formula into it:
=IF([Sprinkler Head Count (Actual)]@row>[Sprinkler Head Count (Estimated)]@row, 1)
Then hide that column since you don't need to look at it on your sheet. Then in the column you want the count use this formula:
=COUNTIF([Sprinkler (helper)]:[Sprinkler (helper)], 1)
Answers
-
create a checkbox column with this formula:
=if(Sprinkler Head Count (Actual) >Sprinkler Head Count (Estimated),1,0)
Then you can do a countif statement counting the checkboxes: =countif([checkbox column]:[checkbox column],1)
-
Sorry forgot the brackets: =if([Sprinkler Head Count (Actual)] >[Sprinkler Head Count (Estimated)],1,0)
-
I'm not sure if it can be done with a single formula. Here's what I would do.
Create a helper column that's a checkbox column called something like "Sprinkler (helper)". Write this formula into it:
=IF([Sprinkler Head Count (Actual)]@row>[Sprinkler Head Count (Estimated)]@row, 1)
Then hide that column since you don't need to look at it on your sheet. Then in the column you want the count use this formula:
=COUNTIF([Sprinkler (helper)]:[Sprinkler (helper)], 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!