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:

[TABLE="width: 218"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Computer[/TD]
[TD]Service[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD]PC01[/TD]
[TD]Service 1[/TD]
[TD]Running[/TD]
[/TR]
[TR]
[TD]PC01[/TD]
[TD]Service 2[/TD]
[TD]Stopped[/TD]
[/TR]
[TR]
[TD]PC01[/TD]
[TD]Service 3[/TD]
[TD]Stopped[/TD]
[/TR]
[TR]
[TD]PC02[/TD]
[TD]Service 1[/TD]
[TD]Running[/TD]
[/TR]
[TR]
[TD]PC02[/TD]
[TD]Service 2[/TD]
[TD]Running[/TD]
[/TR]
[TR]
[TD]PC02[/TD]
[TD]Service 3[/TD]
[TD]Running[/TD]
[/TR]
[TR]
[TD]PC03[/TD]
[TD]Service 1[/TD]
[TD]Stopped[/TD]
[/TR]
[TR]
[TD]PC03[/TD]
[TD]Service 2[/TD]
[TD]Running[/TD]
[/TR]
[TR]
[TD]PC03[/TD]
[TD]Service 3[/TD]
[TD]Running[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

yes, it does ignore the blank rows.
but don't take the ranges too big unnecessary as the array formulas will slow down excel with a large dataset.