Joshua has attended:
Excel Introduction course
Excel Intermediate course
Excel Advanced course
Countif and sumif
Hi, i am trying to create a formula that looks at a workflow tracker for insurance policies that renew monthly and at what stage they are currently are in. We currently have the fields such as submission, quoted, Policy bound etc.
I currently have a Countif formula that counts the total amount of each section but i also want to be able to do this monthly and as the as the year progresses. My thoughts on this would be that each month is in number order, i.e January = 1 and then the formula could include less than or equal to the number selected, meaning if i chose Month 3 i'd have the full data for Jan feb and March
It's just tying it together i am struggling
RE: Countif and sumif
Hi Joshua!
You're on the right track with using COUNTIF and SUMIF for tracking workflow stages. To incorporate the month filtering logic, you can use a combination of COUNTIFS or SUMIFS with a month column in your data.
Here’s how you can structure it:
________________________________________
Assumptions
Let’s say your tracker has these columns:
Policy Name Stage Renewal Date Premium
ABC123 Submission 01/01/2025 1000
DEF456 Quoted 15/02/2025 1200
GHI789 Policy Bound 10/03/2025 1500
You’ll need a helper column to extract the month number from the Renewal Date:
=MONTH(C2)
Let’s say this is in column D (MonthNum).
________________________________________
✅ COUNTIFS Example
To count how many policies are in "Quoted" stage up to and including March (Month 3):
=COUNTIFS(B:B,"Quoted",D:D,"<=3")
________________________________________
SUMIFS Example
To sum the premiums for policies in "Policy Bound" stage up to and including March:
=SUMIFS(D:D,B:B,"Policy Bound",C:C,"<=" & DATE(2025,3,31))
Or if you're using the MonthNum helper column:
=SUMIFS(E:E,B:B,"Policy Bound",D:D,"<=3")
________________________________________
Making It Dynamic
You can use a cell (say G1) where you input the month number (e.g., 3 for March), and reference it like this:
=COUNTIFS(B:B,"Quoted",D:D,"<=" & G1)
________________________________________
Would you like help building a dashboard or summary table that updates automatically based on the selected month? I can help with that too!
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
