Reporting Sum Counts and Sum Totals in weekly/monthly formats

KIMST

I am looking whether to create a Sheet Summary Field or Report based on Sheet for the following. I am also struggling with the correct formula, as well.

Would like to report total # of claims and total sum of account balances for Closed Claims weekly and monthly.

Weekly: would like the process to automatically update on Friday afternoons, reporting Mon-Fri results (work week)

Monthly: I would like to report this as an automatic running cumulative (day by day- adding up to the month)

Columns:
Initial Status (report # of status 'closed' claims)
Account Balance (report sum of all 'closed' claims)

This is the formula that I used for attempting a week report:

=SUMIFS([Account Balance]:[Account Balance], [Initial Status]:[Initial Status], "Closed", [Close Date]:[Close Date], >= TODAY() - WEEKDAY(TODAY()) + 2, [Close Date]:[Close Date], <= TODAY() - WEEKDAY(TODAY()) + 6)

Suggestions on where and how I should report this? I should also mention that I need both of these elements to report to a dashboard.

jmyzk_cloudsmart_jp

Hi @KIMST

Great question! You're definitely on the right track for automating weekly and monthly reporting. Here's a way to do it using the Close Date column,

Step 1: Add helper checkbox columns

https://app.smartsheet.com/b/publish?EQBCT=596595fa4d484e62be27d195c0caa344

Add two new checkbox columns to your sheet:

• This Week
• This Month

Use these formulas:

This Week:

=IF(ISDATE([Close Date]@row), AND(WEEKNUMBER([Close Date]@row) = WEEKNUMBER(TODAY()), YEAR([Close Date]@row) = YEAR(TODAY())))

This Month:

=IF(ISDATE([Close Date]@row), AND(MONTH([Close Date]@row) = MONTH(TODAY()), YEAR([Close Date]@row) = YEAR(TODAY())))

These formulas check if a row has a valid Close Date and whether that date falls within the current week or month. They also handle year-end situations correctly.

Step 2: Create reports

Create two separate reports:

• One filtered where "This Week" is checked
• One filtered where "This Month" is checked

This way, only closed claims from this week or this month will appear.

Step 3: Add Report Summary fields

In each report, use the summary section to:

• Count the number of rows with a Close Date (this gives you the number of closed claims)
• Sum the Account Balance column (this gives you the total balance of those claims)

Step 4: Add reports to a dashboard

You can display the summaries or the full report using widgets in a dashboard for easy tracking at the end of each week or month.

https://app.smartsheet.com/b/publish?EQBCT=35b52ff612e84ad1b127e5412c806f9f