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
I hope I understood you correct. Please see my example and look if this is what you are looking for.
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.)
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
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.myonlinetraininghub.com/excel-pivottable-distinct-count
or
Excel PivotTables Unique Count 3 Ways for Excel 2007 and 2010 (and even 2003): https://www.myonlinetraininghub.com/excel-pivottables-unique-count-3-ways
Thanks, Mynda!!
Glad it was helpful, Marilyn 🙂
