Counting rows regardless of whether dat is present 1

[shawnmandel]

This is probably a pretty trivial question, but is there a formula that counts the number of rows in a column ranged regardless of whether there's data present?

- COUNT just gives me the number of rows with actualt data
- COUNTA gives me the number of rows containing any info

Is there a COUNT function that simply counts the rows whether there's data or not?

Thanks,
Shawn

 

[shawnmandel]

In other words I need to be able to count data, any other info, and any blank/null data in between the rows in a column range.

Thanks,
Shawn

 

[shawnmandel]

As an FYI,

I actually need a non-VBA formula in a cell with the row count.

 

[shawnmandel]

I found this partial solution:

COUNTA(range)+COUNTIF(range,"")

However, in my range, I also have blanks AFTER my data/info. Is there any possible way to exclude all blanks AFTER the rows with info and to just count the blanks in-between?

Thanks,
Shawn

 

[kphu]

you can use rows("range") to give you how many rows in the range

 

[shawnmandel]

Thanks, kphu. However, as I mentioned my last message, I need to exclude all blanks only AFTER that last row with data in the range.

Thanks,
Shawn

 

[shawnmandel]

Here is an example:

I have a static range of A1:A9 -


Row

1 SKU01
2 SKU13
3
4 SKU55
5
6 SKU80
7 SKU71
8
9

Since I need to include all blanks between data, but exclude all blanks after the last row with data, I need to achieve a count of 7 rows.

When I use the following formulas, I get a count of 9, which is not correct for my purposes:

=COUNTA(A1:A9)+COUNTIF(A1:A9,"")
=ROWS(A1:A9)
=COUNTA(A1:A9)+COUNTBLANKS(A1:A9)
=COUNTA(range)+SUM(IF(ISBLANK(range),1,0))
=(MAX(ROW(range))-MIN(ROW(range))+1)*(MAX(COLUMN(range))-MIN(COLUMN(range))+1)


Is there any way to achieve my count of 7?

Regards,
Shawn

 

[shawnmandel]

Hi,

mbarron resolved my issue with the following post in the MS Office forum:


Assuming your data is in only one column the following will count the total number of cells in a range and the subtract the number of blank cells at the bottom of the column:

Function blanks(myrange) As Integer

For Each ccell In myrange
ccount = ccount + 1
If ccell.Value = "" Then
maxblank = maxblank + 1
Else
maxblank = 0
End If

blanks = ccount - maxblank
Next

End Function

Mike

 

[KenWright]

No need for code:-

=MAX((NOT(ISBLANK(A1:A65535))*ROW(A1:A65535)))-MIN(IF((NOT(ISBLANK(A1:A65535))*ROW(A1:A65535))>0,(NOT(ISBLANK(A1:A65535))*ROW(A1:A65535))))+1

Array entered

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[apeoortje]

These may help

Counts the values in the range E4:E23 =COUNT(E4:E23)
Counts all non-blank cells in the range E4:E23 =COUNTA(E4:E23)
Counts blank cells in the range E4:E23 =COUNTBLANK(E2:E23)
Counts all values greater than 0 =COUNTIF(E4:E23;">0")
Counts all values less than 0 =COUNTIF(E4:E23;"<0")
Counts all cells containing the text yes =COUNTIF(F4:F23;"yes")
Counts all cells containing text starting with k =COUNTIF(F4:F23;"k*")
Counts all cells containing text with the letter a =COUNTIF(F4:F23;"*a*")
Counts all cells containing the text yes or no =COUNTIF(F4:F23;"yes")+COUNTIF(F4:F23;"no")
Same as above with an array formula {=SUM((F4:F23="yes")+(F4:F23="no"))}
Counts all cells containing 3 characters =COUNTIF(F4:F23;"???")
Counts all cells with values between 30 and 80 =COUNTIF(E4:E23;">=30")-COUNTIF(E4:E23;">80")
Counts all unique numbers in the range =SUM(IF(FREQUENCY(E4:E23;E4:E23)>0;1;0))
Counts all odd numbers in the range (array formula) {=SUM(MOD(G4:G23;2))}
Counts all even numbers in the range (array formula) {=SUM(IF(MOD(G4:G23;2)=0;1;0))}
Counts cells with an AND-criteria (array formula) {=SUM(IF((F4:F23="yes")*(G4:G23>10);1;0))}
Counts cells with an AND-criteria (array formula) {=SUM((F5:F24="yes")*(G5:G24>10))}
Counts cells with an OR-criteria (array formula) {=SUM((F4:F23="yes")+(F4:F23="no"))}