how could I count minus cells in a column excluding the last one?-CodePudding

I want to count on another sheet that how many people have a minus balance. Of course, I should use COUNTIF, but how could I dynamically narrow the range from C2 to the second last non-blank cell(because there's a chance for the total balance to be minus, it should be excluded)?

CodePudding user response：

Try below formula-

=COUNTIFS(C2:INDEX(C2:C,COUNTA(C2:C)-1),"<0")

Here C2:INDEX(C2:C,COUNTA(C2:C)-1) will return a array of values as well cell reference from C2 to last second non empty cell in column C (Assume you do not have any blank rows inside data). If you have blank row, then you have to use different approach. See this