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:

ComputerServiceState
PC01Service 1Running
PC01Service 2Stopped
PC01Service 3Stopped
PC02Service 1Running
PC02Service 2Running
PC02Service 3Running
PC03Service 1Stopped
PC03Service 2Running
PC03Service 3Running

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>
 
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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
1ComputerServiceStateStateUnique
2PC01Service 1RunningStopped2
3PC01Service 2StoppedRunning3
4PC01Service 3Stopped
5PC02Service 1Running
6PC02Service 2Running
7PC02Service 3Running
8PC03Service 1Stopped
9PC03Service 2Running
10PC03Service 3Running

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
194​

Array Formulas
CellFormula
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))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
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.

ComputerServiceStateHelperRefHelperCountComputer StoppedCount
PC01Service 1RunningPC01-Running10
PC01Service 2StoppedPC01-Stopped1PC012
PC01Service 3StoppedPC01-Stopped20
PC02Service 1RunningPC02-Running10
PC02Service 2RunningPC02-Running20
PC02Service 3RunningPC02-Running30
PC03Service 1StoppedPC03-Stopped1PC031
PC03Service 2RunningPC03-Running10
PC03Service 3RunningPC03-Running20

<tbody>
</tbody>


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
1ComputerServiceStateStateUnique
2PC01Service 1RunningStopped2
3PC01Service 2StoppedRunning3
4PC01Service 3Stopped
5PC02Service 1Running
6PC02Service 2Running
7PC02Service 3Running
8PC03Service 1Stopped
9PC03Service 2Running
10PC03Service 3Running

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
194​

Array Formulas
CellFormula
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))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
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
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
194​

Array Formulas
CellFormula
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))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
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

E
  • Solved
Macro stops working before end sub with no prompt
2 3
Replies
21
Views
1K
_eNVy_
E
drom
  • Solved
Count unique values for filtered column
Replies
1
Views
114
myall_blues
myall_blues
K
  • Solved
Compare multiple columns in 2 workbook's different worksheet, then copy and past values
Replies
2
Views
848
sijpie
sijpie
B
  • Question
Formula for count unique value in large data with one criteria?
Replies
5
Views
381
Peter_SSs
Peter_SSs
S
  • Solved
Lookup Value based upon criteria
Replies
5
Views
431
Fluff
Fluff

Forum statistics

Threads
1,214,516
Messages
6,119,980
Members
448,934
Latest member
audette89

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