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 cou...
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"))
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 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,"<>")- This resulted in same outcome:
SUM(N(TOCOL('All Defects'!I:I,1)<>"Closed"))
COUNTIFS('All Defects'!I:I,"<>Closed",'All Defects'!I:I,"<>")
Also, I'm using this formula: =COUNTIF('All Defects'!I:I,"Closed") for the exact same array and the number is accurate and doesn't count the header.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"))- The CountIFs formula worked! I had to change the range from I2:I100 to I2:I600 but it's finally calculating correctly! Thanks so much