Creating a report or dashboard that identifies those who met sales bonus criteria

IBG_rs

I think this is going to be pretty straightforward, but my DIY attempts have come up empty so far.

Essentially we need an end-of-month report to quickly identify which agents qualified for a monthly bonus based on this basic MONTHLY BONUS CRITERIA:

1) Total net annual premium (NAP) of $15,000 for the month

2) At least one closed sale for each week of that month (we call this being "Submit perfect")

Each week we enter the sales data into our our master Agent Sales Activity smartsheet and this sheet contains columns including A) agent name, B) week of the year, C) weekly net sales revenue, and D) a checkbox for recording if at least one sale was made that week. So far in 2024 we've entered every week's data into the same smart sheet and have used a report to summarize the monthly SUM of sales per agent for the desired month, and a summary COUNT of total "Submit" (week with 1 or more sales). But I can't figure out a way to filter the report to only show those who meet the MONTHLY BONUS CRITERIA.

In an effort to move toward more SmartSheet automation we're trying to improve on our previous workflow which consisted of entering this data into Excel or Google Sheets, with each month of the year's data recorded on a monthly tab/sheet in the yearly spreadsheet workbook. Then we used a SUM for each month and then had a conditional format set up to highlight those who met both criteria for that month/tab.

How could we achieve something similar (and perhaps better) in Smartsheet?

KPH

Glad I could help @IBG_rs . I hope everyone gets their bonuses.

KPH

I may not have quite understood your data, but here are some ideas:

1) This formula will give you the total NAP for the agent on the row where the FH Week contains the word "Mar"

=SUMIFS(NAP:NAP, [Agent Name]:[Agent Name], [Agent Name]@row, [FH Week]:[FH Week], CONTAINS("Mar", @cell))

2) This formula will count the number of submit checkboxes checked for the agent on the row where the FH Week contains the word "Mar"

=COUNTIFS([Agent Name]:[Agent Name], [Agent Name]@row, [FH Week]:[FH Week], CONTAINS("Mar", @cell), Submit:Submit, 1)

3) You can combine those with an IF function that will check a box if the SUMIFS is greater than 15000 and the COUNTIFS is 4.

=IF(AND(SUMIFS(NAP:NAP, [Agent Name]:[Agent Name], [Agent Name]@row, [FH Week]:[FH Week], CONTAINS("Mar", @cell)) > 15000, COUNTIFS([Agent Name]:[Agent Name], [Agent Name]@row, [FH Week]:[FH Week], CONTAINS("Mar", @cell), Submit:Submit, 1) = 4), 1)

All the parts in bold would need to be changed for different months, but you could specify these in a table somewhere to make this easier.

You can then use the checkbox to control your conditional formatting, to filter in a report, and much more, should you want to.

Here is an illustration to hopefully explain it. I have guessed that you have one row per person. If that is wrong, this will not help.

In my example, as I only have 2 weeks and want someone to get a bonus, I reduced the threshold and used 1500 and 2, like this:

=IF(AND(SUMIFS(NAP:NAP, [Agent Name]:[Agent Name], [Agent Name]@row, [FH Week]:[FH Week], CONTAINS("Mar", @cell)) > 1500, COUNTIFS([Agent Name]:[Agent Name], [Agent Name]@row, [FH Week]:[FH Week], CONTAINS("Mar", @cell), Submit:Submit, 1) = 2), 1)

I hope this is helpful.

IBG_rs

This is WONDERFUL and it worked flawlessly. Thank you! One simple follow-up request. Today we decided to split our combined bonus into two bonuses, each with half of the criteria.

Production bonus column with a checkbox for 15K/mo

Submit bonus column with a checkbox for submitting all (4 or 5) weeks of the month

I tried to dissect the longer formula you provided (that worked for the combined bonus) and I tried adapting the shorter formulas in their new respective columns, but to no avail.

I so appreciated any follow-up help!

KPH

Production bonus

Put the sum of the NAP into an IF with a >15000 logic and 1 to check the box if true.

=IF(SUMIFS(NAP:NAP, [Agent Name]:[Agent Name], [Agent Name]@row, [FH Week]:[FH Week], CONTAINS("Mar", @cell))>15000,1)

Submit bonus

Do the same with the count formula

=IF(COUNTIFS([Agent Name]:[Agent Name], [Agent Name]@row, [FH Week]:[FH Week], CONTAINS("Mar", @cell), Submit:Submit, 1)=4,1)

IBG_rs

Wonderful, yet again!!! Thank you very much, KPH!

KPH

Glad I could help @IBG_rs . I hope everyone gets their bonuses.