How to write a PowerShell code to get the count of number of items from a SharePoint list based on a condition

Question

In this SharePoint list there is a column named College. How do I retrieve the count of items who have been to that particular college using PowerShell script from that list

Answer 1

The following PowerShell script for your reference.

$siteUrl="http://sp2013/sites/team"
$listName="CollegeList"
$college="College1"

$web = get-spweb $siteUrl
$list = $web.lists[$listName]
$spQuery = New-Object Microsoft.SharePoint.SPQuery 
$spQuery.Query="<Where><Eq><FieldRef Name='College'/><Value Type='Text'>"+$college+"</Value></Eq></Where>" 
$items = $list.GetItems($spQuery)
write-host $items.Count

Answer 2

You can also try using PnP PowerShell. It has the lot of extension powershell commands targeting SharePoint object. Below is the sample PnP Powershell commands returns you the count of list items from list based on the condition.

PS C:\ktskumar> $cred = Get-Credential
PS C:\ktskumar> Connect-PnPOnline -Url https://tenant.sharepoint.com -Credential $cred
PS C:\ktskumar> $itemsCount = Get-PnPListItem -List "Sample List" -Query "<View><Query><Where><Eq><FieldRef Name='Category'/><Value Type='Text'>SampleCategory</Value></Eq></Where></Query></View>"
PS C:\ktskumar> $itemsCount.Count

WebURL: https://tenant.sharepoint.com List: Sample List FieldName: Category FieldValue: SampleCategory

I hope this helps you to get the ListItems count.

Answer 3

Here is the bit tricky one:

$siteUrl="http://spsite/sites/team"

$listName="Test1"

Connect-PnPOnline -Url $siteUrl -Credential(Get-Credential) Get-PnPList | Where-Object {$_.Title -Contains $listName} | Select-Object Title, ItemCount