CountIF Formula Help

[bflan0524]

hello
i have a sheet that looks like below layout with unique identifiers in the columns. Looking for a formula to help tell me how many of each identifier there is. for example results would be ABC=2 BCD=3 EFG= 7
Note some columns could be blank, not sure if that impacts anything..ty

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E [/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]ABC[/TD]
[TD]BCD[/TD]
[TD]BCD[/TD]
[TD]BCD[/TD]
[TD]EFG[/TD]
[TD]EFG[/TD]
[TD]EFG[/TD]
[TD]EFG[/TD]
[TD]EFG[/TD]
[TD]EFG[/TD]
[TD]EFG[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Try:

=COUNTIF(A1:L1,"ABC")

 

[bflan0524]

Try:

=COUNTIF(A1:L1,"ABC")

thanks for the response, my concern would be there is like 4K of rows. so there would be no way to be able to insert all the possible combos of "ABC" etc
does that make sense

 

[Joe4]

thanks for the response, my concern would be there is like 4K of rows. so there would be no way to be able to insert all the possible combos of "ABC" etc
does that make sense

One way is to copy the list to another sheet.
Then, sort the list and use the "Remove Duplicates" functionality in Excel to remove all the duplicate records, so you are left with one of each one.
Then, the the COUNTIF function in the next column to count the number of each entry from the data on the original sheet.

 

[Joe4]

Also, you may be able to use a Pivot Table or Subtotals.
See: https://www.extendoffice.com/documents/excel/2127-excel-pivot-table-count-unique-values.html
and https://support.office.com/en-us/ar...orksheet-81335b1b-d5e8-4f42-ae72-245b948c45bd

 

[bflan0524]

so i can't do that because the sheet actually looks something like this
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]A13455678[/TD]
[TD]ABC[/TD]
[TD]ABC[/TD]
[TD]BCD[/TD]
[TD]BCD[/TD]
[TD]BCD[/TD]
[TD]EFG[/TD]
[TD]EFG[/TD]
[TD]EFG[/TD]
[TD]EFG[/TD]
[TD]EFG[/TD]
[TD]EFG[/TD]
[TD]EFG[/TD]
[/TR]
[TR]
[TD]B12345678[/TD]
[TD]HIJ[/TD]
[TD]HIJ[/TD]
[TD]HIJ[/TD]
[TD]HIJ[/TD]
[TD]HIJ[/TD]
[TD]HIJ[/TD]
[TD]HIJ[/TD]
[TD]KLM[/TD]
[TD]KLM[/TD]
[TD]KLM[/TD]
[TD]KLM[/TD]
[TD]KLM[/TD]
[/TR]
</tbody>[/TABLE]

so column A also has a unique identifier, so there are 4K rows with these, then columns B-M is who managed column A during that time frame
does that make sense
****** id="cke_pastebin" style="position: absolute; top: 64.8px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]HIJ[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[Eric W]

Maybe it would make sense if you showed us what you want the results to look like. Do you want a table in each row, starting in column N that has the unique identifiers in that row and the count? Do you want a table on another sheet with the column A identifier and the persons managing that resource with it?

 

[bflan0524]

ideally yes as you explained where at the end column N for example would show ABC=2 column O would show BCD=3 etc
if that isnt the most efficient way to do it , i am open to ideas

 

[Eric W]

While you could do that with native formulas, generally speaking "unique" formulas are computationally intensive and complicated, and on 4K rows, I suspect it would bog down your sheet. You could try a UDF (User-Defined Function).

Open a copy of your workbook. Right click on the sheet tab on the bottom and select View Code. From the menu, click Insert > Module. In the sheet that opens, paste this code:

Public Function UniqCount(ByVal MyRange As Range, ByVal ctr As Long)
Dim cl As Range, MyDict As Object, a As Variant, b As Variant

    Application.Volatile
    UniqCount = ""
    Set MyDict = CreateObject("scripting.Dictionary")
    For Each cl In MyRange
        MyDict(CStr(cl.Value)) = MyDict(CStr(cl.Value)) + 1
    Next cl
    On Error Resume Next
    a = MyDict.keys
    b = MyDict.items
    UniqCount = a(ctr - 1) & " = " & b(ctr - 1)
    
End Function

Press Alt-Q to close the editor.

In N2, place this formula:

=UniqCount($B2:$M2,COLUMNS($N2:N2))

Drag it down and to the right as needed. I suspect this might bog down your sheet as well, but try it and see. You may want to try a macro that reads the entire sheet and places the results when you call the macro.