Forum Discussion
Count if function
=SUM(N(TOCOL('All Defects'!I:I,1)<>"Closed")). This count is 173 when the actual count is 172. Any idea of what could be contributing to this? Thanks
- Could it be counting the header at the top of the column?
Apologies, I think i was responding to the slicer question in my earlier reply. If it's counting the header, how do i exclude it? Also, I have other formulas referencing the "I" column and the counts are accurate. Another question, what does the "1" (in red) in the below formula represent? Again, Thanks for all of your help.
=SUM(N(TOCOL('All Defects'!I:I,1)<>"Closed"))
TOCOL converts an array to a vertical column. The '1' tells the function to ignore blanks when converting the array. It's one way to remove the blanks before the counting is done.
Below is the heading. I've recreated this slicer multiple times and can't get rid of this duplicate. I did notice that in the pivot table, the duplicate is reflected also and again, this does not reflect the data source which lists Assigned only once.
- Please check your other discussion. I posted a solution there. Thank you.