 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2008Counting Non Blank Cells
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 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)  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- >I could really use some help with this headache. > [quoted text clipped - 24 lines] > > Thank you so much for your help....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 > In Sheet1, try something like: > =SUMPRODUCT(--(ISNUMBER(MATCH(A2:A10000,Sheet2!C2:C8000,0)))) [quoted text clipped - 34 lines] >> >> Thank you so much for your help....AJ > 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. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- >I could really use some help with this headache. > [quoted text clipped - 23 lines] > >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 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 >>I could really use some help with this headache. >> [quoted text clipped - 51 lines] > > --ron >Thanks, Ron. > [quoted text clipped - 14 lines] > >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
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.