 |
 | 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 2008COUNTA formula with indirect reference - help please!
Hi-- I am getting strange results with a formula. I would like to use COUNTA to count non blanks in a worksheet called "Data". I'd like it to take column values from cells C2 and D2 to get the range of data in "Data" to count. What I have is: =COUNTA("Data!"&$C$2&ROW()&":Data!"&$D$2&ROW()) However, this always returns 1 regardless of the number of blanks. I think my problem is related to the following. Just playing around, I tried a direct formula to count nonblanks in columns AE:AJ: =COUNTA(Data!AE3:Data!AJ3) --- Works correctly =COUNTA("Data!AE3:Data!AJ3") --- always returns 1 like my example above Any suggestions for how to get the first formula to work correctly? Thanks! Maybe... =COUNTA(INDIRECT("Data!"&$C$2&ROW()&":"&$D$2&ROW())) Your formula which boils down to this: =COUNTA("Data!AE3:Data!AJ3") Is counting how many things are inside those (). In this case you have a single string, so you get 1. =counta("a","b") would return 2 =COUNTA(Data!AE3:Data!AJ3) More often written like: =COUNTA(Data!AE3:AJ3) will count the non-empty cells in AE3:AJ3 of the Data worksheet. > Hi-- > [quoted text clipped - 17 lines] > > Thanks!  SignatureDave Peterson You can build up a cell or range address as a string (as you are trying to do), but you must use the INDIRECT function to get Excel to recognise it. Try this: =COUNTA(INDIRECT("Data!"&$C$2&ROW()&":Data!"&$D$2&ROW())) Hope this helps. Pete > Hi-- > [quoted text clipped - 17 lines] > > Thanks! Try it like this: =COUNTA(INDIRECT("Data!"&$C$2&ROW()&":"&$D$2&ROW())) SignatureBiff Microsoft Excel MVP > Hi-- > [quoted text clipped - 17 lines] > > Thanks! Hi all-- Thanks so much for the replies. Unfortunately, all of these formulas continue to give me the value of "1" when I put them in my spreadsheet. Any other ideas? Jason See this screencap: http://img142.imageshack.us/img142/6184/indirectej3.jpg The formula does work. I just used it on the same sheet so that you can see it does return the correct results. SignatureBiff Microsoft Excel MVP > Hi all-- > Thanks so much for the replies. Unfortunately, all of these formulas > continue to give me the value of "1" when I put them in my > spreadsheet. Any other ideas? > > Jason You are right...I am not sure what I did wrong before. Thanks!! Getting all of those "stupid" quotes in the correct order can be a challenge sometimes! That's what usually trips people up. Thanks for feeding back! SignatureBiff Microsoft Excel MVP > You are right...I am not sure what I did wrong before. Thanks!!
|
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.