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

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    Answer ✓

    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
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @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)

Answers

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    Answer ✓

    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
    Michael Culley ✭✭✭✭✭

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

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @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)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!