• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 568
  • Last Modified:

How to "group count" in Excel?

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,
1 Solution
RyanProject Engineer, ElectricalCommented:
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.
Elton PascuaCommented:
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.
Patrick MatthewsCommented:
Why not use a PivotTable?
Suggest you may want to use the frequency function.  See the following link http://office.microsoft.com/en-us/excel-help/count-occurrences-of-values-or-unique-values-in-a-data-range-HP003056118.aspx

I have also used sumproduct for this at times.


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now