Formula that counts all data in rows including blanks 3

[shawnmandel]

Is there any formula that exists in Excel that counts all data including any other info, and blanks?

For example:

COUNT would count the cells with data
COUNTA would count cells with any other info

I actually need a formual that would count all info in cells including empty cells in a specific range.

Any help is greatly appreciated.

Thanks,
Shawn

 

[slurpee55]

COUNT(range) will count the non-empty cells, and COUNTIF(range,"") will return the number of empty cells, so =(COUNT(A1:H1)+COUNTIF(A1:H1,"")) should do the trick.

 

[shawnmandel]

slurpee, thanks so much. I'm almost there.

Now, in my range, I also have a few blanks at the end. Is there any possible way to only count the blanks in-between rows and to exclude all blanks after rows with any info?

Thanks so much for your response,
Shawn

 

[AnotherHiggins]

slurpee55,

Close. Count only counts cells with numbers. CountA will count any non-blank cell. so
[COLOR=blue white]=counta(a1:h1)+countif(a1:h1,"")[/color]
should work.


[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[mbarron]

These will return the number of cells in a rectangular range. They need to be entered as an array formula (ctrl + shift + enter)

=(MAX(ROW(range))-MIN(ROW(range))+1)*(MAX(COLUMN(range))-MIN(COLUMN(range))+1)

=COUNTA(range)+SUM(IF(ISBLANK(range),1,0))

Mike

 

[slurpee55]

Good point, thanks. I don't work much with alphabetical characters - I missed that.

 

[shawnmandel]

mbarron,

Do I need to use BOTH formulas in order to achieve my row count (including all blanksd between the data), but excluding all blanks after real data in the range?

Thanks,
Shawn

 

[shawnmandel]

mbarron,

Unfortunately, it appears that the following arraw formulas appears to be counts all data in rows including blanks AFTER the non-blank data.

Thanks,
Shawn

 

[mbarron]

The formula are independent of each other.


I'm confused by your last post. I've tried removing data from all locations in my range and have gotten the same result from each formula.

Mike

 

[shawnmandel]

mbarron,

Sorry, let me clarify:

I have the following data in a single column range A1:A9 -


1 SKU1
2 SKU12
3 SKU76
4
5 SKU48
6
7 SKU011
8
9


I need to get a count of the number of rows with SKUs including the blanks ONLY in-between (i.e., rows 4 & 6), but excluding the blanks in rows 8 & 9.

My problem is that I keep getting a count of 9 with the formula =COUNTA(A1:A9)+COUNTIF(A1:A9,"")

I basically need a count of 7 as in the example above.


I hope I'm being more clear.


Regards,
Shawn

 

[shawnmandel]

More detail:

I have a static range of A1:A9 -


Row

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

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

 

[AnotherHiggins]

What kind of data is in the other columns? Is there a column that will always be populated for every row you want to count?

I'm just trying to figure out what the bigger picture is here - there might be another way to proceed. Otherwise, I don't know how you're going to accomplish this without VBA.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[shawnmandel]

John, I never know the number of rows I will have in my columns, therefore, I set a range larger than all of the rows that I will every need. Thus, I will always have a range that spans more rows than I have data. So, I will always have some number of empty cells after my data--which I will want to exclude.

If I can't get a non-VBA solution, then, worse-case, I'll have to settle for a VBA solution.


Much appreciated,
Shawn

 

[mbarron]

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

 

[shawnmandel]

Mike, can I use this function in a cell from within an Excel sheet?

Thanks,
Shawn

 

[shawnmandel]

Mike, it works!!

Thanks so much for you help.

 

[AnotherHiggins]

Excluding empty cells is easy...it's just excluding some empty cells that's going to be hard.

What I'm thinking is if you import data that looks something like this:[tt]
A B C
SKU1 description Other
SKU12 description Other
SKU76 description Other
Other
SKU48 description Other
description Other
SKU011 description Other[/tt]

Then a simple =counta(C:C) would do.

*EDIT* Dang, Mike...ya beat me. I was just working on a UDF. Not really pretty, but it will always return the final cell in column A:

Function MyCount() As Integer
MyCount = Range("a65536").End(xlUp).Row
End Function

and just type =mycount() into a cell.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[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

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

 

[AnotherHiggins]

Nice, Ken. I thought there must be a way to do it w/o code, but couldn't figure it out.

to you.

I've really got to learn more about array formulas. I just checked, but there doesn't seem to be a FAQ on them. Any ideas where I can get more info?

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[KenWright]

Cheers John

Chip Pearson has a page on Arrays:-

http://cpearson.com/excel/array.htm

and Bob Phillips' page on SUMPRODUCT has a fair bit about the way arrays work as a result of the SUMPRODUCT function. Principles hold good for other array formulas though.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

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

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