1

enter image description here

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)?

zzzgoo
  • 1,974
  • 2
  • 17
  • 34

1 Answers1

2

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 post by @TheMaster

  • COUNTA(C2:C) will count all non blank cells in C column then minus 1 from that count to make it second non empty cell till down to up.

  • And then apply COUNTIFS() function.

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • Is there any official document telling why the return value of `INDEX` could be used in the cell reference, i.e. `C2:INDEX(...)`? – idfurw Dec 15 '22 at 03:17
  • Besides, could the column letter also be referred in a similar way without the use of `INDIRECT`? I have tried `A1:ADDRESS(2,2)` but it does not work. – idfurw Dec 15 '22 at 03:25
  • 1
    I do not look for official documentation for this but there are lot of post around blogs regarding this specially volatile vs non volatile functions. In stack-overflow there are many post. – Harun24hr Dec 15 '22 at 03:36
  • 1
    @idfurw https://stackoverflow.com/a/74281216/5632629 – player0 Dec 15 '22 at 07:19
  • 1
    https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd – zzzgoo Dec 17 '22 at 07:25