Can I use the Countif function to count the amount of times one column is greater than another?

lcain

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!

Michael Culley

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)

Mike TV

@lcain

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)

Michael Culley

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)

Michael Culley

Sorry forgot the brackets: =if([Sprinkler Head Count (Actual)] >[Sprinkler Head Count (Estimated)],1,0)

Mike TV

@lcain

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)