 |
 | 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 / Worksheet Functions / February 2007COUNTIF(LEFT('SpreadSheet1!A1:A25, 1), "A")
How would I create this function to do a Count If the left character in a range of cells is an "A"? COUNTIF(LEFT('SpreadSheet1!A1:A25, 1), "A") Thank you. COUNTIF('SpreadSheet1!A1:A25,"A*")  SignatureKL [MVP - Microsoft Excel] RU: http://www.mvps.ru/Program/Default.aspx ES: http://mvp.support.microsoft.com/?LN=es-es EN: http://mvp.support.microsoft.com/?LN=en-us Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36 > How would I create this function to do a Count If the left character in a > range of cells is an "A"? > > COUNTIF(LEFT('SpreadSheet1!A1:A25, 1), "A") > > Thank you. Thanks that did the trick... > COUNTIF('SpreadSheet1!A1:A25,"A*") > [quoted text clipped - 4 lines] >> >> Thank you. I like COUNTIF but don't include the ' in front of the sheet name. You can eliminate the sheet reference alltogether if you name the range, for example suppose I name it D for data. Then here are some of your possible formulas: =COUNTIF(D,"a*") =SUMPRODUCT(N(LEFT(D)="a")) =SUM(N(LEFT(D)="a")) entered as an array Note since you are looking for the first character you don't need to specify the second argument of the LEFT function. Also, although it is in vogue to use -- to convert TRUE,FALSE to 1,0 N is shorter in this case. Note also that none of these formulas are case sensitive, but you can do a case sensitive version. SignatureCheers, Shane Devenshire > COUNTIF('SpreadSheet1!A1:A25,"A*") > [quoted text clipped - 4 lines] > > > > Thank you. "ShaneDevenshire" <ShaneDevenshire@discussions.microsoft.com> wrote >I like COUNTIF but don't include the ' in front of the sheet name. I guess that's an obvious missprint deserving no comments :-) > =SUMPRODUCT(N(LEFT(D)="a")) The subtle difference is that the SUMPRODUCT formula is about 12 times slower than the COUNTIF one in 5000 cells ;-) > =SUM(N(LEFT(D)="a")) entered as an array This one is even slower than SUMPRODUCT, although the gap is far less significant > Also, although it is in vogue to > use -- to convert TRUE,FALSE to 1,0 N is shorter in this case. It is shorter to write, but seems to be slightly slower to calculate (according to C.Williams' plug-in) Regards, KL Hi KL, It wasn't a typo on your part, it was in the original question. The most important point in my comment is that of using range names. And like I said COUNTIF is the approach of choice. On average array entered formulas are about 1.1 - 1.2 times slower than SUMPRODUCT although the files size may be greater for the latter. Most people are more interested in speed, not file size. SignatureCheers, Shane Devenshire > "ShaneDevenshire" <ShaneDevenshire@discussions.microsoft.com> wrote > [quoted text clipped - 17 lines] > Regards, > KL =SUMPRODUCT(--(LEFT('SpreadSheet1!A1:A25, 1)="A")) > How would I create this function to do a Count If the left character in a > range of cells is an "A"? > > COUNTIF(LEFT('SpreadSheet1!A1:A25, 1), "A") > > Thank you. |
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.