Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count interstitial blank cells?
How can I count the number of blank cells between
the row above and the last nonblank row above, if the cell in this row is nonblank? That is, the count is one or more only in the first nonblank cell following one or more blank cells. For example, suppose A1:A2 are nonblank, A3:A5 are blank, and A6:A9 are nonblank. I want B6 to count 3 and B10 to count 4. All other cells in B:B should be blank (or zero). I know I can do this easily in VBA. Is there some way to write a cell formula that only uses built-in Excel functions -- preferrably not even add-ins? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count interstitial blank cells?
Bryan Hessey wrote:
testing column A, and using column C as a helper column, in C2 put =IF(A2<"","",IF(C1="",1,C1+1)) and in B2 put =IF(A3="","",C2) and formula drag B2 and C2 down to cover your data. Well, I think I screwed up my example, but I got the idea. Works great! Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count interstitial blank cells?
Good to see it works for you, and thanks for the response -- Wrote: Bryan Hessey wrote: testing column A, and using column C as a helper column, in C2 put =IF(A2<"","",IF(C1="",1,C1+1)) and in B2 put =IF(A3="","",C2) and formula drag B2 and C2 down to cover your data. Well, I think I screwed up my example, but I got the idea. Works great! Thanks. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=533202 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count number of cells | Excel Worksheet Functions | |||
Generating truly blank cells | Excel Worksheet Functions | |||
formula to count cells not blanK | Excel Worksheet Functions | |||
Blank Cells in Pivot Tables | Excel Discussion (Misc queries) | |||
Can I count how many grey-filled cells are in a row ? | Excel Worksheet Functions |