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]
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Computer[/td][td=bgcolor:#5B9BD5]Service[/td][td=bgcolor:#5B9BD5]State[/td][td][/td][td=bgcolor:#70AD47]Computer[/td][td=bgcolor:#70AD47]State[/td][td=bgcolor:#70AD47]Count[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PC01[/td][td=bgcolor:#DDEBF7]Service 1[/td][td=bgcolor:#DDEBF7]Running[/td][td][/td][td=bgcolor:#E2EFDA]PC01[/td][td=bgcolor:#E2EFDA]Stopped[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]PC01[/td][td]Service 2[/td][td]Stopped[/td][td][/td][td]PC03[/td][td]Stopped[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PC01[/td][td=bgcolor:#DDEBF7]Service 3[/td][td=bgcolor:#DDEBF7]Stopped[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]PC02[/td][td]Service 1[/td][td]Running[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PC02[/td][td=bgcolor:#DDEBF7]Service 2[/td][td=bgcolor:#DDEBF7]Running[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]PC02[/td][td]Service 3[/td][td]Running[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PC03[/td][td=bgcolor:#DDEBF7]Service 1[/td][td=bgcolor:#DDEBF7]Stopped[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]PC03[/td][td]Service 2[/td][td]Running[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PC03[/td][td=bgcolor:#DDEBF7]Service 3[/td][td=bgcolor:#DDEBF7]Running[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
Upvote 0
sandy666 said:
like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Computer[/td][td=bgcolor:#5B9BD5]Service[/td][td=bgcolor:#5B9BD5]State[/td][td][/td][td=bgcolor:#70AD47]Computer[/td][td=bgcolor:#70AD47]State[/td][td=bgcolor:#70AD47]Count[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PC01[/td][td=bgcolor:#DDEBF7]Service 1[/td][td=bgcolor:#DDEBF7]Running[/td][td][/td][td=bgcolor:#E2EFDA]PC01[/td][td=bgcolor:#E2EFDA]Stopped[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]PC01[/td][td]Service 2[/td][td]Stopped[/td][td][/td][td]PC03[/td][td]Stopped[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PC01[/td][td=bgcolor:#DDEBF7]Service 3[/td][td=bgcolor:#DDEBF7]Stopped[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]PC02[/td][td]Service 1[/td][td]Running[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PC02[/td][td=bgcolor:#DDEBF7]Service 2[/td][td=bgcolor:#DDEBF7]Running[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]PC02[/td][td]Service 3[/td][td]Running[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PC03[/td][td=bgcolor:#DDEBF7]Service 1[/td][td=bgcolor:#DDEBF7]Stopped[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]PC03[/td][td]Service 2[/td][td]Running[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PC03[/td][td=bgcolor:#DDEBF7]Service 3[/td][td=bgcolor:#DDEBF7]Running[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
Click to expand...

we don't know much if you don't show the formula
 
Upvote 0
I think Sandy is just querying if that is the format that the op would like to see. If so then a SUMPRODUCT will do, linking up to the Computer and the State. Given the data in the example then something like this for the count of PC01:

=SUMPRODUCT(($A$2:$A$10="PC01")*($C$2:$C$10="Stopped"))

If this is the format required I would copy column A to begin with and paste in to say, column E, then use the built in remove duplicates tool (Data tab -> Remove Duplicates) to get a unique list, then link the SUMPRODUCT formula to the cell of the PC number and drag it down.
 
Last edited:
Upvote 0
Thanks for the reply.

No, all I need for my reporting is to have a count of the total number of PCs where a service is stopped. So The formula i'm looking for is to give me a total number of PCs that have one or more services stopped. What those services are is irrelevant and it doesn't make a difference to my reporting whether a single service or all services are stopped. So all it would nee to to is give me a number of PCs with issues.

Hope that makes sense. Let me know if you have any other questions
 
Upvote 0
like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Computer[/td][td=bgcolor:#5B9BD5]Service[/td][td=bgcolor:#5B9BD5]State[/td][td][/td][td=bgcolor:#70AD47]State[/td][td=bgcolor:#70AD47]Count[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PC01[/td][td=bgcolor:#DDEBF7]Service 1[/td][td=bgcolor:#DDEBF7]Running[/td][td][/td][td=bgcolor:#E2EFDA]Stopped[/td][td=bgcolor:#E2EFDA]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]PC01[/td][td]Service 2[/td][td]Stopped[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PC01[/td][td=bgcolor:#DDEBF7]Service 3[/td][td=bgcolor:#DDEBF7]Stopped[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]PC02[/td][td]Service 1[/td][td]Running[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PC02[/td][td=bgcolor:#DDEBF7]Service 2[/td][td=bgcolor:#DDEBF7]Running[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]PC02[/td][td]Service 3[/td][td]Running[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PC03[/td][td=bgcolor:#DDEBF7]Service 1[/td][td=bgcolor:#DDEBF7]Stopped[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]PC03[/td][td]Service 2[/td][td]Running[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PC03[/td][td=bgcolor:#DDEBF7]Service 3[/td][td=bgcolor:#DDEBF7]Running[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Code: 
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"State"}, {{"Count", each Table.RowCount(_), type number}}),
    Filter = Table.SelectRows(Group, each ([State] = "Stopped"))
in
    Filter[/SIZE]
 
Upvote 0
@AlanY

formula (?) doesn't matter if result is not like OP want so I need to know first if result is ok then I can post M-code
 
Upvote 0
Yes, that look to be what I am after.

I'm a little confused as to how that code works though. where do I put it?

Apologies if this is a bit of a basic question, I'm not THAT advanced with excel.
 
Upvote 0
If you want a standard Excel formula then use a COUNTIF function,

=COUNTIF(C2:C10,"Stopped")

will return how many PCs have the state "Stopped"
 
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
89
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
355
Jagadeshrao
J
T
  • Solved
Index Match for when Values in a column are repeated
Replies
2
Views
171
TerryE123
T
drom
  • Solved
Count unique values for filtered column
Replies
1
Views
2K
myall_blues
myall_blues

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

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