View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
zvkmpw zvkmpw is offline
external usenet poster
 
Posts: 153
Default Best way to create a sorting table with non numerical values

I was looking for a way to have a field/button that could help me
display either : All date, 07/18 or 08/18


Here's one way, starting with the input data and result data arranged as before.

Step 1: Set up filtering by selecting A1:A5 and using
Home Editing Sort & Filter Filter
(I use Excel 2010; other versions might vary.)
This enables either viewing all rows or selecting rows by date.

Step 2: In G2, enter
=SUBTOTAL(103, A2)
and copy down to G5. This returns 1 for visible rows; 0 for hidden rows.

Step 3: In B9, I put
=COUNTIFS(B$2:B$5, $A9, $G$2:$G$5, 1)
and copy it into B9:F10. This limits the counting to visible rows only.