 |
 | 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 / June 2004Counting in a filtered column
Hello all, I am looking for a way of counting the amount of times "PASS" appear in column B wether or not column A is filtered. I have trie countif(B1:B10,"PASS") but when column A is filtered the total cel counting "PASS" in column B still shows the overall total. I need it t only show how many "PASS" are now in column B. Thanks in advance. Keep up the great work -- Message posted from http://www.ExcelForum.com Have a look at HELP index for SUBTOTAL  SignatureDon Guillett SalesAid Software donaldb@281.com > Hello all, > I am looking for a way of counting the amount of times "PASS" appears [quoted text clipped - 7 lines] > --- > Message posted from http://www.ExcelForum.com/ Don, thank you for you help, I sort of understand the SubTotal in HELP but how do I subtotal column B so that after filtering column A it only counts how many "PASS" there are. At present if I subtotal column B it still counts all the "PASS" and "FAIL" I need it to subtotal only the "PASS" after filtering. Greg. --- Message posted from http://www.ExcelForum.com/ By "now in column B" I assume you mean "now visible in column B". If so, here's one way: =SUMPRODUCT(--(rng="PASS"),SUBTOTAL(3,OFFSET(INDEX(rng,1,1), ROW(INDIRECT("1:" & ROWS(rng)))-1,0))) where rng is your target range (B1:B10 in this case). If you want to hard-code your range: =SUMPRODUCT(--(B1:B10="PASS"),SUBTOTAL(3,OFFSET(B1, ROW(INDIRECT("1:10")-1,0))) > Hello all, > I am looking for a way of counting the amount of times "PASS" appears > in column B wether or not column A is filtered. I have tried > countif(B1:B10,"PASS") but when column A is filtered the total cell > counting "PASS" in column B still shows the overall total. I need it to > only show how many "PASS" are now in column B. |
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.