Search code examples
arraysexcelcountformula

Excel 365 Function to Count Occurrence of Array List In Original List

I have a list of company names in the sheet "Defects". I built a unique list of those companies in a sheet called "Report" using this function utilizing an array list.

=SORT(UNIQUE(Defects!E:E,,TRUE),,1)

This works great. Since the array is dynamic, it can grow and shrink. I want to do a count of the number of times a single item from the array appears in the "Defects" sheet.

In the "Defects" sheet This is my complete list

  E     
1 Acme        
2 Gadget
3 Widget      
4 Acme
5 Widget
6 Widget

This is what I want in the "Report" sheet.

  Company     Count 
1 Acme        2
2 Gadget      1
3 Widget      3

I can't seem to make a function that will take a specific value in the array as an input as a COUNT or COUNTIF function. This function gives me all values of 1.

=COUNT(MATCH(A20,Defects!E:E,0))

A simple COUNTIF gives me an error and turns the formula into text.

=COUNTIF(Defects!E:E,A1))

Any help is appreciated.

Solution

  • Use LET and CHOOSE with COUNTIFS:

    =LET(x,SORT(UNIQUE(E1:E6)),CHOOSE({1,2},x,COUNTIFS(E:E,x)))

    enter image description here

    Or if you really want full column references:

    =LET(y,E:E,x,SORT(UNIQUE(FILTER(y,y<>""))),CHOOSE({1,2},x,COUNTIFS(y,x)))