sumproduct

[Nena]

Originally theses macros worked out perfectly. The macro was setup to
divide the information within the one column into two categories: if
there was data in the column than it would be a resident and if there
was not text than it would be a commuter.

However the problem I have now is that I do not care about the cell
that contains no data and only would like to count the cells that
contain data. For instance, the name of the column is BUILDINGS and
the data within the column are the name of the buildings.

So now what I would like to know is how X buildings there are or Y
buildings there are but I also have to account for those blank cells
without including them.

Any suggestions?


Range("D15") = Evaluate("=sumproduct((Fall_2006_Cohort!
N2:N65536="""")*(Fall_2006_Cohort!AB2:AB65536))")

Range("D16") = Evaluate("=sumproduct((Fall_2006_Cohort!
N2:N65536<>"""")*(Fall_2006_Cohort!AB2:AB65536))")

Thanks in advance.

 

[Don]

Originally theses macros worked out perfectly. The macro was setup to
divide the information within the one column into two categories: if
there was data in the column than it would be a resident and if there
was not text than it would be a commuter.

However the problem I have now is that I do not care about the cell
that contains no data and only would like to count the cells that
contain data. For instance, the name of the column is BUILDINGS and
the data within the column are the name of the buildings.

So now what I would like to know is how X buildings there are or Y
buildings there are but I also have to account for those blank cells
without including them.

Any suggestions?

Range("D15") = Evaluate("=sumproduct((Fall_2006_Cohort!
N2:N65536="""")*(Fall_2006_Cohort!AB2:AB65536))")

Range("D16") = Evaluate("=sumproduct((Fall_2006_Cohort!
N2:N65536<>"""")*(Fall_2006_Cohort!AB2:AB65536))")

Thanks in advance.

Couldn't you just use countA of the column?

Select the cells
Application.WorksheetFunction.CountA(Selection)

Or replace Selection with the range reference of the cells you're
counting.