counting unique values where those value match a criteria

Aeropars

New Member
Joined
May 23, 2019
Messages
7
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]
 
try this


Book1
ABCDEF
1ComputerServiceStateStateUnique
2PC01Service 1RunningStopped2
3PC01Service 2StoppedRunning3
4PC01Service 3Stopped
5PC02Service 1Running
6PC02Service 2Running
7PC02Service 3Running
8PC03Service 1Stopped
9PC03Service 2Running
10PC03Service 3Running
194
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
 
Last edited:
Upvote 0
try this

ABCDEF
ComputerServiceStateStateUnique
PC01Service 1RunningStopped
PC01Service 2StoppedRunning
PC01Service 3Stopped
PC02Service 1Running
PC02Service 2Running
PC02Service 3Running
PC03Service 1Stopped
PC03Service 2Running
PC03Service 3Running

<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.
 
Upvote 0
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.
 
Upvote 0
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
ABCDEF
1ComputerServiceStateStateUnique
2PC01Service 1RunningStopped2
3PC01Service 2StoppedRunning3
4PC01Service 3Stopped
5PC02Service 1Running
6PC02Service 2Running
7PC02Service 3Running
8PC03Service 1Stopped
9PC03Service 2Running
10PC03Service 3Running
194
Cell Formulas
RangeFormula
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.
 
Upvote 0
if you've a large dataset, it's better revise the formula to rid of the empty rows

ABCDEF
ComputerServiceStateStateUnique
PC01Service 1RunningStopped
PC01Service 2StoppedRunning
PC01Service 3Stopped
PC02Service 1Running
PC02Service 2Running
PC02Service 3Running
PC03Service 1Stopped
PC03Service 2Running
PC03Service 3Running

<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
 
Upvote 0
try this


Book1
ABCDEFG
1ComputerServiceStateStateUnique
2PC01Service 1RunningPCStopped2
3PC01Service 2StoppedServerStopped3
4PC01Service 3Stopped
5PC02Service 1Running
6PC02Service 2Running
7PC02Service 3Running
8PC03Service 1Stopped
9PC03Service 2Running
10PC03Service 3Running
11Server01Service 1Running
12Server01Service 2Stopped
13Server01Service 3Stopped
14Server02Service 1Running
15Server02Service 2Running
16Server02Service 3Running
17Server03Service 1Stopped
18Server03Service 2Running
19Server03Service 3Running
20Server04Service 1Stopped
194
Cell Formulas
RangeFormula
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.
 
Upvote 0
try this

ABCDEFG
ComputerServiceStateStateUnique
PC01Service 1RunningPCStopped
PC01Service 2StoppedServerStopped
PC01Service 3Stopped
PC02Service 1Running
PC02Service 2Running
PC02Service 3Running
PC03Service 1Stopped
PC03Service 2Running
PC03Service 3Running
Server01Service 1Running
Server01Service 2Stopped
Server01Service 3Stopped
Server02Service 1Running
Server02Service 2Running
Server02Service 3Running
Server03Service 1Stopped
Server03Service 2Running
Server03Service 3Running
Server04Service 1Stopped

<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?
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top