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"))
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.
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