Forum Discussion
Solved
SUM formula calculating 1 more than actual
The count formula below is calculating one more than when i filter on the column.
=SUM(N(TOCOL('All Defects'!I:I,1)<>"Closed")). This count is 174 when the actual count is 173. Any idea of what could be contributing to this? Thanks
That's okay if the value in I1 is not "Closed".
=COUNTIFS('All Defects'!I2:I100,"<>Closed",'All Defects'!I2:I100,"<>") =SUM(N(TOCOL('All Defects'!I2:I100,1)<>"Closed"))
7 Replies
Hi Lisa, here's a breakdown of the formula. It's possible some of your cells may have a space or two.
How did you calculate the "actual count"?
And why use such a complicated way for counting? Why not a simple COUNTIF()?
- The actual count was just filtering on the column.
I needed to count all cells in a column that were not "Closed" and also excluding the counting of any blank cells. The below formula gave me a count of over 1 million when it should have been 178.
=COUNTIF('All Defects'!I:I,"<>Closed")I::I includes the column header in I1.
=COUNTIFS('All Defects'!I:I,"<>Closed",'All Defects'!I:I,"<>")
