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]

try this

Book1
	A	B	C	D	E	F
1	Computer	Service	State		State	Unique
2	PC01	Service 1	Running		Stopped	2
3	PC01	Service 2	Stopped		Running	3
4	PC01	Service 3	Stopped
5	PC02	Service 1	Running
6	PC02	Service 2	Running
7	PC02	Service 3	Running
8	PC03	Service 1	Stopped
9	PC03	Service 2	Running
10	PC03	Service 3	Running
194

Cell Formulas
Range	Formula
F2	{=SUM(IF(FREQUENCY(IF($C$2:$C$10=E2,MATCH($A$2:$A$10,$A$2:$A$10,0)),ROW($C$2:$C$10)-ROW($C$2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aeropars]

what is your Excel version?

I'm using Office 365.

Cheers

 

[sandy666]

If 365...
1. Create Table from your range (Ctrl+T)
2. Data - New Query - From Other Sources - Blank Query then replace all there with copied code from the post.

Note.

this line contain name of the Table

Source = Excel.CurrentWorkbook(){[Name="[B][COLOR="#0000FF"]Table1[/COLOR][/B]"]}[Content],

so if your original table has different name change it in the code

btw.
What is PowerQuery
MS PowerQuery for Excel
Getting Started with Get&Transform

 

[Aeropars]

try this

	A	B	C	D	E	F
Computer	Service	State	State	Unique
PC01	Service 1	Running	Stopped
PC01	Service 2	Stopped	Running
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 style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

194​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IF($C$2:$C$10=E2,MATCH($A$2:$A$10,$A$2:$A$10,0)),ROW($C$2:$C$10)-ROW($C$2)+1),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

this seems to be spot on on my initial test. I've quite a large dataset to crunch so i'll have a play around and get back to you.

Many thanks for all your help so far.

 

[AlanY]

you're welcome

 

[miss_ell]

If you want to know which computers have stopped and not just how many, perhaps my solution, though not perfect, can be a start. You would need helper columns though.

I haven't been here in a while so I forgot how to post a spreadsheet sample like Alan above.

[TABLE="width: 1232"]
<tbody>[TR]
[TD]Computer[/TD]
[TD]Service[/TD]
[TD]State[/TD]
[TD]HelperRef[/TD]
[TD]HelperCount[/TD]
[TD][/TD]
[TD]Computer Stopped[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]PC01[/TD]
[TD]Service 1[/TD]
[TD]Running[/TD]
[TD]PC01-Running[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]PC01[/TD]
[TD]Service 2[/TD]
[TD]Stopped[/TD]
[TD]PC01-Stopped[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]PC01[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]PC01[/TD]
[TD]Service 3[/TD]
[TD]Stopped[/TD]
[TD]PC01-Stopped[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]PC02[/TD]
[TD]Service 1[/TD]
[TD]Running[/TD]
[TD]PC02-Running[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]PC02[/TD]
[TD]Service 2[/TD]
[TD]Running[/TD]
[TD]PC02-Running[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]PC02[/TD]
[TD]Service 3[/TD]
[TD]Running[/TD]
[TD]PC02-Running[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]PC03[/TD]
[TD]Service 1[/TD]
[TD]Stopped[/TD]
[TD]PC03-Stopped[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]PC03[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PC03[/TD]
[TD]Service 2[/TD]
[TD]Running[/TD]
[TD]PC03-Running[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]PC03[/TD]
[TD]Service 3[/TD]
[TD]Running[/TD]
[TD]PC03-Running[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


D2 = A2&"-"&C2
E2 =COUNTIF($D$1:D2,D2)
G2 =IF(AND(RIGHT(D2,LEN(D2)-5)="Stopped",E2=1),LEFT(D2,4),"")
H2 =COUNTIFS($A:$A,G2,$C:$C,"Stopped")


Then copy down, and filter by column G.

 

[AlanY]

this seems to be spot on on my initial test. I've quite a large dataset to crunch so i'll have a play around and get back to you.

Many thanks for all your help so far.

if you've a large dataset, it's better revise the formula to rid of the empty rows

Book1
	A	B	C	D	E	F
1	Computer	Service	State		State	Unique
2	PC01	Service 1	Running		Stopped	2
3	PC01	Service 2	Stopped		Running	3
4	PC01	Service 3	Stopped
5	PC02	Service 1	Running
6	PC02	Service 2	Running
7	PC02	Service 3	Running
8	PC03	Service 1	Stopped
9	PC03	Service 2	Running
10	PC03	Service 3	Running
194

Cell Formulas
Range	Formula
F2	{=SUM(IF(FREQUENCY(IF($A$2:$A$25<>"",IF($C$2:$C$25=E2,MATCH($A$2:$A$25,$A$2:$A$25,0))),ROW($C$2:$C$25)-ROW($C$2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aeropars]

if you've a large dataset, it's better revise the formula to rid of the empty rows

	A	B	C	D	E	F
Computer	Service	State	State	Unique
PC01	Service 1	Running	Stopped
PC01	Service 2	Stopped	Running
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 style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

194​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IF($A$2:$A$25<>"",IF($C$2:$C$25=E2,MATCH($A$2:$A$25,$A$2:$A$25,0))),ROW($C$2:$C$25)-ROW($C$2)+1),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

If I wanted to take this a step further, is there a way to do exactl the same count but to only count devices in column A that start with 'PC'?

At some point I'm expecting to have different device types such as servers in column A but for statistical analysis we want to differenciate between the two device types.

so that would be something like:

PC Stoppes: 10
Server stopped: 3

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G
1	Computer	Service	State			State	Unique
2	PC01	Service 1	Running		PC	Stopped	2
3	PC01	Service 2	Stopped		Server	Stopped	3
4	PC01	Service 3	Stopped
5	PC02	Service 1	Running
6	PC02	Service 2	Running
7	PC02	Service 3	Running
8	PC03	Service 1	Stopped
9	PC03	Service 2	Running
10	PC03	Service 3	Running
11	Server01	Service 1	Running
12	Server01	Service 2	Stopped
13	Server01	Service 3	Stopped
14	Server02	Service 1	Running
15	Server02	Service 2	Running
16	Server02	Service 3	Running
17	Server03	Service 1	Stopped
18	Server03	Service 2	Running
19	Server03	Service 3	Running
20	Server04	Service 1	Stopped
194

Cell Formulas
Range	Formula
G2	{=SUM(IF(FREQUENCY(IF(LEFT($A$2:$A$25,2)=LEFT(E2,2),IF($C$2:$C$25=F2,MATCH($A$2:$A$25,$A$2:$A$25,0))),ROW($C$2:$C$25)-ROW($C$2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aeropars]

try this

	A	B	C	D	E	F	G
Computer	Service	State	State	Unique
PC01	Service 1	Running	PC	Stopped
PC01	Service 2	Stopped	Server	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
Server01	Service 1	Running
Server01	Service 2	Stopped
Server01	Service 3	Stopped
Server02	Service 1	Running
Server02	Service 2	Running
Server02	Service 3	Running
Server03	Service 1	Stopped
Server03	Service 2	Running
Server03	Service 3	Running
Server04	Service 1	Stopped

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

194​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IF(LEFT($A$2:$A$25,2)=LEFT(E2,2),IF($C$2:$C$25=F2,MATCH($A$2:$A$25,$A$2:$A$25,0))),ROW($C$2:$C$25)-ROW($C$2)+1),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

I've no idea how that works but it does the job! Where would I start to even begin to understand what you're doing there??

Is that also looking to remove the bank rows as you mentioned before?