Get Count and Sum from a SharePoint list column and send Notification in Teams Chat

(2) Share

Report

Posted on by Prem4253

697

Hello,

I have a SharePoint list named as "Surpass Projects". Within this list there are about 10 columns.

From this 10 columns there are 2 columns named as "Project Cost" and "Project Start Date".

My requirement is - everyday at specific time, it should send a notification in a Teams Group Chat about the No. of Count (MTD) and Sum (MTD) from the "Project Cost" column based on Date column (MTD)

See below example in table:

There are 4 entries for Jul 2025. So the result I'm expecting to receive in Teams Group Chat should be as below :

MTD Count = 4

MTD Project Cost = $579.00

Later today, if there are 2 more project added with 100 value each in Project cost, so tomorrow the Counts should be 6 and the sum will be $779.00

Please advise

Categories:

Using flows

I have the same question (0)

All responses (22)

Answers (4)

Verified answer

Expiscornovus 33,830 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @Prem4253,

You can use an OData Filter query to collect the right items. After that you can use a Select to retrieve the cost values and some xpath to calculate the sum. The last bit is inspired by Paul Murana his blog:

https://tachytelic.net/2021/06/power-automate-instant-sum-array/

Below is an example

1. Get Items Filter Query

(ProjectStartDate ge '@{utcNow('yyyy-MM-01T00:00:00')}') and (ProjectStartDate lt '@{addToTime(utcNow('yyyy-MM-01T00:00:00'), 1, 'Month', 'yyyy-MM-ddT00:00:00')}')

2. Compose json for xml conversion

{ "root": { "Numbers": @{body('Select')} } }

3. Body of Chat message
MTD Count: @{length(body('Select'))} MTD Project Cost: @{formatnumber(xpath(xml(outputs('JSON')), 'sum(/root/Numbers)'), 'C2')}

4. Test result

Happy to help out 😁

I share more #PowerAutomate and #SharePointOnline content on my Blog, LinkedIn, Bluesky profile or Youtube Channel

Was this reply helpful? Yes No
Prem4253 697 on at

Like (0)

Report
Copy link

Link copied!
@Expiscornovus

Thanks! for your valuable response.

I did created the flow as instructed in your below post, but I'm getting an Error at second step itself (Get Items Filter Query)

I added below expression with some changes:

utcNow('M/d/yyyy')

addToTime(utcNow('M/d/yyyy'), 1, 'Month', 'M/d/yyyy')

My Time Zone is IST and Project Start Date format is as below:

Please pardon, as I'm not too good at using expressions.

Was this reply helpful? Yes No
Expiscornovus 33,830 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
AnHi @Prem4253,

It looks like your query is missing one closing parentheses ')' just before the 'and'.

The format should be:

(Criteria 1) and (Criteria 2)

And I used the 1st of the month in my expression so that it always looks for a period of the 1st till the end of the month?

Btw, you should be able to copy/paste my example into your filter. No need to type it yourself 🙂

Happy to help out 😁

I share more #PowerAutomate and #SharePointOnline content on my Blog, LinkedIn, Bluesky profile or Youtube Channel

Was this reply helpful? Yes No
Chriddle 8,672 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Check the date format ;)

Was this reply helpful? Yes No
Prem4253 697 on at

Like (0)

Report
Copy link

Link copied!

@Expiscornovus @Chriddle

Yet no luck

Can you please share the expression that you have actually added in the Expression window. Seems I'm missing some small error to identify.

Was this reply helpful? Yes No
Expiscornovus 33,830 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi,

The date time values are missing the closing single quote characters at the end. Add those as well and try again please. Btw. the code snippet and screenshot of the expressions are already shared in step 1 in my first reply.

(Field ge 'Value') and (Field lt 'Value')

Happy to help out 😁

I share more #PowerAutomate and #SharePointOnline content on my Blog, LinkedIn, Bluesky profile or Youtube Channel

Was this reply helpful? Yes No
Prem4253 697 on at

Like (0)

Report
Copy link

Link copied!

@Expiscornovus

I made some progress till getting the count. But not getting the Sum

Was this reply helpful? Yes No
Expiscornovus 33,830 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi,

I renamed the Compose action to JSON. The project cost expression refers to the outputs of that renamed action.

In your setup try

formatnumber(xpath(xml(outputs('Compose')), 'sum(/root/Numbers)'), 'C2')

Happy to help out 😁

I share more #PowerAutomate and #SharePointOnline content on my Blog, LinkedIn, Bluesky profile or Youtube Channel

Was this reply helpful? Yes No
Prem4253 697 on at

Like (0)

Report
Copy link

Link copied!
@Expiscornovus

I have renamed the Compose action to JSON later and added the same expression yet no luck

formatnumber(xpath(xml(outputs('JSON')), 'sum(/root/Numbers)'), 'C2')

Need to understand, what does 'C2' indicates.

And for the SUM, is the date format causing the error. The Date format is MM/dd/yyyy

Was this reply helpful? Yes No
Verified answer

Expiscornovus 33,830 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Hi @Prem4253,

The formatnumber is to turn the sum back into a currency value. The C2 is the currency two decimals format.

Btw, can you share the outputs of your Select action (below is an example of the expected outputs format).

Also make sure you switch the Map field to text mode in the Select Action (with the button on the right side), so that it only is a single column (normal it has two columns, aka key/value mode).

Additionally, only use the Project Cost field in there.

Happy to help out 😁

I share more #PowerAutomate and #SharePointOnline content on my Blog, LinkedIn, Bluesky profile or Youtube Channel

Was this reply helpful? Yes No