August 12, 2016
Hi! I have a large data table (600,000 rows+) that I update every week. I would like to be able to automatically populate a table that counts the total number of instances in Column B (text values) for each unique text value in Column A.
I currently use a pivot table with a slicer to manually count the instances in each category. (I only have 9 unique values in Column A, so it isn't too horrible.)
But I'm sure there must be a better way to do this automatically.
(I'm thinking if there was some sort of "countaif" function, it would do what I'm looking for...)
What would you suggest I try?
Thanks in advance!
--Marilyn
VIP
April 21, 2015
August 12, 2016
Thanks, Franz. Looks like I need to describe my problem statement more completely. Building on your idea of a spreadsheet example, see the attached.
I'm actually looking for the count of unique instances in Column B that apply to the unique instances in Column A. (Note: All like items in Column B will only apply to the same unique item in Column A.)
July 3, 2016
Hi All,
pivot tables are more efficient to achieve these outputs.
One of the formula approaches could be
In H4 to be copied down to get a unique list from A4:A100 (alphabetically ordered)
=IFERROR(INDEX(A$4:A$100,MATCH(0,INDEX(COUNTIF(A$4:A$100,"<"&A$4:A$100)-SUMPRODUCT(COUNTIF(A$4:A$100,H$3:H3)),),0)),"")
in I4 to be copied down
=SUMPRODUCT((A$4:A$100=H4)/COUNTIF(B$4:B$100,B$4:B$100&""))
Hope it helps
August 12, 2016
I found Mynda's blog on April 3rd to be the best response to my question.
Excel PivotTable Distinct Count for Excel 2013 and higher (with Power Pivot): https://www.myonlinetraininghu.....inct-count
or
Excel PivotTables Unique Count 3 Ways for Excel 2007 and 2010 (and even 2003): https://www.myonlinetraininghu.....unt-3-ways
Thanks, Mynda!!
Answers Post
1 Guest(s)