 |
 | 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 / March 2008Count IF Function
Dear all I have a column with multi same entries and i want to count them as per category. So if i have in colum b same entries such as "category a" for 20 entries in a column with 5000 entries and bring them by sort filter, how i can count them after that? Any way to do ti by count if fomrula? Thaks for your time You don't need to filter: =COUNTF(B1:B5000,"catagory a")  SignatureGary''s Student - gsnu200771 Thank you Gary but after filtering i have 250 lines with category a 20 times, category b 100, category c 130 show i want to shee count 3, 1 for category a, category b and category c, the uniq, such group by. Any suggestions? Sorry i didn't make it clear earlier. > You don't need to filter: > =COUNTF(B1:B5000,"catagory a") =SUBTOTAL(103,B2:B5000) returns the number of currently filtered rows. Regards, Stefi „Manos” ezt írta: > Thank you Gary > but after filtering i have 250 lines with category a 20 times, category b [quoted text clipped - 8 lines] > > You don't need to filter: > > =COUNTF(B1:B5000,"catagory a") Thank you Stefi. Ok i count them how can i group by.....? i have the total, inside i want to count only the unic entries... not the double. > =SUBTOTAL(103,B2:B5000) returns the number of currently filtered rows. > Regards, [quoted text clipped - 14 lines] > > > You don't need to filter: > > > =COUNTF(B1:B5000,"catagory a") Maybe you need to sort your table by column B, then subtotal by column B using Count function. Make sure you have a header in row 1! Give an example if you need more help! I don't understand how do you want to count unique entries. Stefi „Manos” ezt írta: > Thank you Stefi. > Ok i count them how can i group by.....? [quoted text clipped - 19 lines] > > > > You don't need to filter: > > > > =COUNTF(B1:B5000,"catagory a") Try also a pivot table, which gets you the results in a matter of seconds. Select just the col B range, inclusive the col label in B1 (let's assume the label is: "Cat"). Click Data > PivotTable. Click Next > Next. In step 3, click Layout. Drag n drop "Cat" into both the ROW and DATA area. Click OK > Finish. That's it. Hop over to the pivot sheet (to the left) for the results, ie a uniques listing of all the categories, and the total counts for each. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- I can not use pivot table here is an example colum b -- > filter --> custtom (begins with 2000) the it gives me 8000 rows. then in thses filter i have common entries. So i want to count the unique entires for example i have 150 rows and the rest are double entries Thank you for your entry > Try also a pivot table, which gets you the results in a matter of seconds. > Select just the col B range, inclusive the col label in B1 (let's assume the > label is: "Cat"). Click Data > PivotTable. Click Next > Next. In step 3, > click Layout. Drag n drop "Cat" into both the ROW and DATA area. Click OK > > Finish. That's it. Hop over to the pivot sheet (to the left) for the results, > ie a uniques listing of all the categories, and the total counts for each. |
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.