counting unique values where those value match a criteria

[Aeropars]

Hi all,

I'm having a bit of a brain explosion over this so should I would register for some help.

I have a spreadsheet which has 3 columns. Column A contains a list of computer names, Column B contains a list of services, column C contains the status of the service.

What I want is to count all the unique values from column A where column C has an entry of 'stopped'. The idea is that I can use this for reporting the number of devices which have any service in a stopped state. an example of the table is as follows:

Computer	Service	State
PC01	Service 1	Running
PC01	Service 2	Stopped
PC01	Service 3	Stopped
PC02	Service 1	Running
PC02	Service 2	Running
PC02	Service 3	Running
PC03	Service 1	Stopped
PC03	Service 2	Running
PC03	Service 3	Running

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

 

[sandy666]

like this?

Computer	Service	State		Computer	State	Count
PC01	Service 1	Running		PC01	Stopped	2​
PC01	Service 2	Stopped		PC03	Stopped	1​
PC01	Service 3	Stopped
PC02	Service 1	Running
PC02	Service 2	Running
PC02	Service 3	Running
PC03	Service 1	Stopped
PC03	Service 2	Running
PC03	Service 3	Running

 

[AlanY]

like this?

Computer	Service	State		Computer	State	Count
PC01	Service 1	Running		PC01	Stopped	2​
PC01	Service 2	Stopped		PC03	Stopped	1​
PC01	Service 3	Stopped
PC02	Service 1	Running
PC02	Service 2	Running
PC02	Service 3	Running
PC03	Service 1	Stopped
PC03	Service 2	Running
PC03	Service 3	Running

we don't know much if you don't show the formula

 

[tyija1995]

I think Sandy is just querying if that is the format that the op would like to see. If so then a SUMPRODUCT will do, linking up to the Computer and the State. Given the data in the example then something like this for the count of PC01:

=SUMPRODUCT(($A$2:$A$10="PC01")*($C$2:$C$10="Stopped"))

If this is the format required I would copy column A to begin with and paste in to say, column E, then use the built in remove duplicates tool (Data tab -> Remove Duplicates) to get a unique list, then link the SUMPRODUCT formula to the cell of the PC number and drag it down.

 

[Aeropars]

Thanks for the reply.

No, all I need for my reporting is to have a count of the total number of PCs where a service is stopped. So The formula i'm looking for is to give me a total number of PCs that have one or more services stopped. What those services are is irrelevant and it doesn't make a difference to my reporting whether a single service or all services are stopped. So all it would nee to to is give me a number of PCs with issues.

Hope that makes sense. Let me know if you have any other questions

 

[sandy666]

like this?

Computer	Service	State		State	Count
PC01	Service 1	Running		Stopped	3​
PC01	Service 2	Stopped
PC01	Service 3	Stopped
PC02	Service 1	Running
PC02	Service 2	Running
PC02	Service 3	Running
PC03	Service 1	Stopped
PC03	Service 2	Running
PC03	Service 3	Running

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"State"}, {{"Count", each Table.RowCount(_), type number}}),
    Filter = Table.SelectRows(Group, each ([State] = "Stopped"))
in
    Filter[/SIZE]

 

[sandy666]

@AlanY

formula (?) doesn't matter if result is not like OP want so I need to know first if result is ok then I can post M-code

 

[Aeropars]

Yes, that look to be what I am after.

I'm a little confused as to how that code works though. where do I put it?

Apologies if this is a bit of a basic question, I'm not THAT advanced with excel.

 

[sandy666]

what is your Excel version?

 

[tyija1995]

If you want a standard Excel formula then use a COUNTIF function,

=COUNTIF(C2:C10,"Stopped")

will return how many PCs have the state "Stopped"