CODEWITHSUNDEEP
excelpowershellcolors
Glowie
Glowie

Reputation: 2309

Powershell to count cells in one column based on filter value

My excel spread sheet has 11028 rows, and two columns.

First column has organizational unit, and second column has value.

In Excel, I can filter the first column so that there are about 100 different organizational units. And for each organizational unit, I want to count (1) total number of rows (2) number of rows with value "Unknown".

If I do this manually, it will take forever, hence I am looking for some sort of API in powershell that will allow me to count number of rows in column B based on filter set in column A.

Is this even possible in Powershell?

Upvotes: 1

Views: 4379

Answers (2)

Doug Finke
Doug Finke

Reputation: 6823

$data = @"
Field1, Field2
a,1
b,2
c,3
a,Unknown
b,5
c,Unknown
a,7
b,8
c,Unknown
c,10
"@ | ConvertFrom-Csv

'Total Rows'
$data | group field1 -NoElement
'Total Rows with Unknown'
$data | Where {$_.field2 -eq 'unknown'} | group field1 -NoElement

Prints

Total Rows

Count Name                     
----- ----                     
    3 a                        
    3 b                        
    4 c                        

Total Rows with Unknown

Count Name                     
----- ----                     
    1 a                        
    2 c

Upvotes: 2

Orbling
Orbling

Reputation: 20612

Try using just Excel.

If you copy your column containing the organisational unit, and use the remove duplicates function on it, you will end up with a column containing the unique entries for organisational units. Then you just need two simple formulas: COUNTIF() and COUNTIFS().

For each organisational unit:

1) total number of rows

Assuming Column A is your organisational unit (the original) and Column D is your unique entries:

=COUNTIF($A$1:$A$10,D1)

That's:

=COUNTIF(organisational unit range, particular organisational unit)

Example layout, showing formula for counting elements in Column A

Count of unique entries in column A

2) number of rows with value "Unknown"

For this you can use COUNTIFS() which takes multiple criteria.

Assuming Column B has your values, some of which are "Unknown".

=COUNTIFS($A$1:$A$10,D1,$B$1:$B$10,"Unknown")

That's:

=COUNTIFS(organisational unit range, particular organisational unit, value range, "Unknown")

Example showing multiple criteria matching of Unknown

Count of entries matching "Unknown"

Upvotes: 3

Related Questions