Counting Non Blank Cells

[AJ]

I could really use some help with this headache.

I'm using Excel 2003.

I'm doing a Vlookup comparing text data in Column A of Worksheet 1 to text
data in Column C of Worksheet 2.
If there's a match I'm having the result placed in Column B of Worksheet 1.
When there's no match I get a blank which is fine.

I'm comparing about 10,000 records in Worksheet 1 to about 8,000 records in
Worksheet 2.

I need a way to get a count of only the matched records in Column B.

I've tried using the The CountA function for Column B, but I get the exact
same counts in Column B as I have for Column A in Worksheet 1, even though
only about 6,000 records matched. Is there a way to get this right? Excel
seems to count all of the cells just because there are formulas in every
cell in Column B.

COUNT doesn't work because I'm not using numerical values, and COUNTBLANKS
isn't finding any blanks in Column B!

Assuming there's a way to do this, I'll then need to compare the records
from Worksheet 2 to Worksheet 1 and get a count of those that match as well.

Thank you so much for your help....AJ

 

[Max]

In Sheet1, try something like:
=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A10000,Sheet2!C2:C8000,0))))

In Sheet2, try something like:
=SUMPRODUCT(--(ISNUMBER(MATCH(C2:C8000,Sheet1!A2:A10000,0))))

You may want to switch calc mode* to manual before you proceed with the
above. It's quite calc intensive since large ranges are involved. Press F9
to recalc.
*via Tools > Options > Calculation tab (options are there)

 

[Ron Rosenfeld]

I could really use some help with this headache.

I'm using Excel 2003.

I'm doing a Vlookup comparing text data in Column A of Worksheet 1 to text
data in Column C of Worksheet 2.
If there's a match I'm having the result placed in Column B of Worksheet 1.
When there's no match I get a blank which is fine.

I'm comparing about 10,000 records in Worksheet 1 to about 8,000 records in
Worksheet 2.

I need a way to get a count of only the matched records in Column B.

I've tried using the The CountA function for Column B, but I get the exact
same counts in Column B as I have for Column A in Worksheet 1, even though
only about 6,000 records matched. Is there a way to get this right? Excel
seems to count all of the cells just because there are formulas in every
cell in Column B.

COUNT doesn't work because I'm not using numerical values, and COUNTBLANKS
isn't finding any blanks in Column B!

Assuming there's a way to do this, I'll then need to compare the records
from Worksheet 2 to Worksheet 1 and get a count of those that match as well.

Thank you so much for your help....AJ

"get a blank"??? In Excel, a "blank" means there is nothing in the cell. I
assume you have some formula in column B -- and a formula is not a <blank>.

Also, I will assume that your formula returns a null string ("") and not a
<space> (" ") if there is not a match. (If you have it returning a string,
then change that.

Given those assumptions, the following formula should return a proper count:

=SUMPRODUCT(--(LEN(B1:B65535)>0))

Note that you cannot refer to the entire column (B:B) in Excel 2003; you can
certainly make the range smaller than I've shown (e.g. B1:B15000), just so
long as it is "long enough".

--ron

 

[AJ]

Thanks for getting back with me, I'll give this a try later today...

I did come up with a formula on my own that seems to work for ColumnB in
Worksheet1 and it is very quick.

In Worksheet 1, Cell B9451, I wrote the following:

=CountIF(b2:b9450,">?")

This function seems to require that at least one text character has to be
present in order for a cell to be counted for the designated range.

Since my Vlookup in Worksheet1 will be compared to at least 6 other tabs and
bring the results to Worksheet1 columns B through G. I plan just to drag
the formula across the columns I need.

Then in each Worksheet2 through Worksheet7, where I'm comparing ColumnC of
each individually back to Worksheet1 ColumnA, I'll place the matched
results to ColumnD. I'll use the formula above to get those counts.

If all of this works like I hope, I then plan to add a little matrix in a
separate tab to summarize the results and spare the readers of wading
through all of the data. Heck, I might through in a chart or two while I'm
at it.

This will be a monthly report. Do you see any reason why my formula won't
do the job?

Thanks again!

AJ

 

[AJ]

Thanks, Ron.

As I just replied to Max, I wrote a little formula that seems to work at
the bottom of ColumnB as follows:

=CountIf(B2:B9450,">?")

It seems to only count cells that contain at least 1 text character, which
is what is needed.

I realize that there are many way to solve tasks in Excel, and I'm not
confident that my solution is bullet proof, but it seems to work. Any
thoughts whether your or Max's formulas would be safer or more reliable
than mine?

Thanks again for your help and quick response!

AJ

 

[Ron Rosenfeld]

Thanks, Ron.

As I just replied to Max, I wrote a little formula that seems to work at
the bottom of ColumnB as follows:

=CountIf(B2:B9450,">?")

It seems to only count cells that contain at least 1 text character, which
is what is needed.

I realize that there are many way to solve tasks in Excel, and I'm not
confident that my solution is bullet proof, but it seems to work. Any
thoughts whether your or Max's formulas would be safer or more reliable
than mine?

Thanks again for your help and quick response!

AJ

So long as you understand what the formula is doing, and that is what you want,
and it works -- those are the important factors.

Not knowing what sorts of results you are producing in Column B, it's difficult
to go further.

--ron

 

[Max]

Thanks for getting back with me, I'll give this a try later today...
No prob, do post back whether the suggestion worked for you as a closure

As for your new query (which should actually be a fresh new posting)
as Ron expressed in his reply to you:

So long as you understand what the formula is doing, and that is what you
want, and it works --
those are the important factors.

---