Count the repetitive value with a formula in an excel report

In some of my reports, I needed to count the duplicate value for 1 or more specific products.

formula excel formula excel

 

When I use the formula ?

To count duplicate value for 1 or more specific groups.

 

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

 

How is/are the formula(s) ?

=COUNTIF()

=COUNTIFS()

In E2, for 1 value:

=COUNTIF(A:A,"group1")

It counts the duplicate value, in this case, “group1”.

In E3, for 2 values:

=COUNTIFS(A:A,"group1",B:B,"=server")

It counts only all servers for the group1.

In E4, for 3 values:

=COUNTIFS(A:A,"group1",B:B,"server",C:C,"unix")

It counts only all unix servers for the group1. Now if I want to count for different groups and for multiple systems, I will use this formula (right picture above):

=COUNTIFS(A:A,{"group1","group3"},B:B,"pc",C:C,E2:E5)

Interesting Topics