counting unique values where those value match a criteria

A

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]
 
Sort by date Sort by votes
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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
AlanY said:
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]
Click to expand...

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
Aeropars said:
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.
Click to expand...

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
AlanY said:
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]
Click to expand...

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
AlanY said:
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]
Click to expand...

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
You must log in or register to reply here.

Similar threads

D
  • Solved
Count total award value per unique supplier
Replies
4
Views
72
Fluff
Fluff
E
  • Solved
Macro stops working before end sub with no prompt
2 3
Replies
21
Views
2K
_eNVy_
E
J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
294
Jagadeshrao
J
T
  • Solved
Index Match for when Values in a column are repeated
Replies
2
Views
167
TerryE123
T
drom
  • Solved
Count unique values for filtered column
Replies
1
Views
2K
myall_blues
myall_blues

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

Share this page

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