Dynamic Range Sizes, including blanks

I have a spreadsheet with two formulas which need to allow for a variable number of rows to calculate. My original solution to this problem does work however it will not work if there are blank rows in the data.

Formula: (CSE)
This is the original formula, the highlighted sections would need to manually updated in order for the formula to return an accurate result when the data set size changes
=SUM(IFERROR(N(FREQUENCY(ABS($K$2:$K$45),ABS($K$2:$K$45))>0)*N($I$2:$I$45<DATE(YEAR($M$2),MONTH($M$2)+1,1))*N($I$2:$I$45>=DATE(YEAR($M$2),MONTH($M$2),1)),""))

Highlighted is what I tried to allow for an automatic update.
=SUM(IFERROR(N(FREQUENCY(ABS(OFFSET(K2,,,COUNTA(K:K))),ABS(OFFSET(K2,,,COUNTA(K:K))))>0)*N(OFFSET(I2,,,COUNTA(I:I))<DATE(YEAR($M$2),MONTH($M$2)+1,1))*N(OFFSET(I2,,,COUNTA(I:I))>=DATE(YEAR($M$2),MONTH($M$2),1)),""))

This works fine, but it does not capture the number of bottom rows equivalent to the number of blanks - 1 (4 blanks in the data will give a range 3 rows short of the bottom)


More details and sample data in the attached file. See Green Comments in the file. Red Cells on the bottom of I, J, K, are being ignored by my formula in O11, and P11. EDIT: It is the bottom THREE red cells, I had 4 highlighted in the sheet by accident.

Thanks
xf091214.xlsx

Also, I understand these formulas could easily be replaced by a super basic pivot table. I am using formulas instead for a reason.

Solution posted in another thread (on the tips section)

Originally Posted by sweep

There is also a non volatile alternate, if you can reasonably estimate the maximum number of rows likely to be employed:

=SUM(K2:INDEX(K2:K10000,MATCH(9.99999999999E+307,K2:K10000,1)))

This will sum all entries between K2 and the last entry in column K (up to K10000), regardless of blanks.

This appears to solve my issue.



Edit: Nevermind, but I think it's close.


=SUM(IFERROR(N(FREQUENCY(ABS(K2:INDEX(K2:K10000,MATCH(9.99999999999E+307,K2:K10000,1))),ABS(K2:INDEX(K2:K10000,MATCH(9.99999999999E+307,K2:K10000,1))))>0)*N(K2:INDEX(K2:K10000,MATCH(9.99999999999E+307,K2:K10000,1))<DATE(YEAR($M$2),MONTH($M$2)+1,1))*N(K2:INDEX(K2:K10000,MATCH(9.99999999999E+307,K2:K10000,1))>=DATE(YEAR($M$2),MONTH($M$2),1)),""))

Suggestions?

=SUM(INDIRECT("K2"&":"&"K"&sumif($K$2:$K$50,"<>0")+1))

I would solve this with an pivot table.

See the attached file.

oeldere,

That formula does work for the total SUM I am looking for, however it doesn't give me the total number of rows, including blanks, within the range.

For the following:

1
1
2
1

3

1

2
4

5

I need the return of 5, because there are 5 unique values in that table. My current formula returns a 3, because the RED numbers, equivalent to the total COUNT of numbers, are the only ones being selected. There are 9 total values in there, 5 unique values and 4 blanks. The first 9 rows, including blanks, only include three of the uniques so that is all that will be counted.

Edit: Pivot table is not an option for this particular problem unfortunately.

Why not sort the data from lowest to highest, then you don't have empty rows, and the data are more structured?

I bet you have a reason for that.

It is already sorted by another column, unfortunately there can be blank rows in the columns I need to look at for this particular formula, while the rest of the columns have data.