Counting data - sorta! 1

[cbs604]

I have a SS of 8 columns and over 50,000 rows with a mixture of blank spaces and numerical data.

I need to count cells with data in them starting from column A, and stop counting when the first blank cell is reached.

In other words, if cells A1 to H1 have data in them EXCEPT C1 and F1, then I want to count "2". If cell A1 is empty, then "0", if all are not empty then "8", and so on.

Don't ask why, it's what my customer wants, and I didn't ask as well.

This data was originally in Access, and I thought it might be easier to do this in Excel.

Any clues would be most appreciated

Cheers,
Brodie

 

[SkipVought]

Hi,

There may be a way to do it using sheet functions, but here's a UDF. Paste into a MODULE and run as you would a sheet function.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[SkipVought]

Function CountTilBlank(r As Range) As Integer
    With r
        If .Cells(1, 1) = "" Then
            CountTilBlank = 0
        Else
            If .Columns.Count > 1 Then
                If .Cells(1, 2) = "" Then
                    CountTilBlank = 1
                Else
                    CountTilBlank = .End(xlToRight).Column - .Column + 1
                End If
            Else
                If .Cells(2, 1) = "" Then
                    CountTilBlank = 1
                Else
                    CountTilBlank = .End(xlDown).Row - .Row + 1
                End If
            End If
        End If
    End With
End Function

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[yogia]

Hi Brodie:

Let me see if I understood you correctly ...

I have used only 11 rows for columns A:H, please adjust the number of rows to suit your requirement.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

Hi Brodie:

Sorry, I made a mistake in the last post ... but I don't know how to edit it, so let me post it again ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

Hi Brodie:

And here is one using a normal formula ...

The part ... ROW(INDIRECT("1:"&(ROWS(A1:H11)*COLUMNS(A1:H11))))

in the illustration can be replaed for simplification with either ...

1) COUNTA(A1:H11)+COUNTIF(A1:H11,"")
or
2) SUM(COUNTIF(A1:H11,{"<>*","*"}))


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[cbs604]

Thanks Skip and Yogia, some good stuff to try here.

Yogia, in your example:-


J2 should be 2,
J5 should be 1,
J6 should be 5,

all the others should be 8.

Cheers,
Brodie

 

[yogia]

Hi Brodie:

Let me see if I understood your specifications in the preceding post ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[cbs604]

A star for you Yogi.

That worked brilliantly first time.

Many thanks

Cheers,
Brodie

 

[yogia]

You Are Very Welcome Brodie ... now let us keep EXCELing!

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com