How to "group count" in Excel?

Posted on 2012-08-17
Last Modified: 2012-09-04
Dear Excel experts,
Here is the background:
I have a script that pulls all the “alarms” from all the “servers” from the environment.
The script output is csv file that holds the following columns and data:
•      Time of the alarm.
•      Component
•      Alarm type
•      Server ID
•      And other data that not relevant to the question.

Some information about the relation of the items:
•      For each component there are several “alarm type”  and each “alarm type” belong  to one specific component.

•      Each alarm can have more than one instance (==line) on a server (ID).

Each component has

I would like to pull from this CSV file the following:
•      Number of unique alarm currently in the system (basically to do some kind of “grouping” based on Alarm type.

•      Number of unique alarm for each component (grouping based  on ”component” and “alarm type”)

Or in other words, how do you I do “group  count” in excel?

Thanks in advance,
Question by:WAS_Infra
    LVL 13

    Expert Comment

    You can "Remove duplicates" based only on Alarm type, then do the same with Type and Component.

    You can also sort by these 2 columns, then do a grouping, with a count function to get a similar result, but I think this will be harder to read for you.
    LVL 8

    Expert Comment

    by:Elton Pascua
    Here's an implementation of Cpearson's Distinct Values UDF and also native countifs function in Excel. Please see the attached files for sample implementation and let us know if it's what you need.
    LVL 92

    Accepted Solution

    Why not use a PivotTable?
    LVL 1

    Expert Comment

    Suggest you may want to use the frequency function.  See the following link

    I have also used sumproduct for this at times.


    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now