To see how much health cover is left, list every claim with its member, status and settled amount, then subtract the total settled from the base sum insured. A small sheet does this in two formulas. It gives you an estimate for planning; the insurer's own balance statement is the figure that counts, so keep it beside your sheet.
What columns does the running sheet need?
Put the claim list on one tab, starting in row 2 with headings in row 1:
- A: Claim ID, B: Member, C: Hospital, D: Admission date, E: Amount claimed, F: Amount settled, G: Status (Pending, Settled, Rejected).
- Add a column for the insurer's reference number, so each row can be matched to a letter.
On a second tab, one row per member, or one row for the whole family if your policy is a floater with a shared sum insured:
| A: Member | B: Base cover | C: Settled so far | D: Balance |
|---|---|---|---|
| Self | ₹5,00,000 | =SUMIFS(Claims!$F$2:$F$200, Claims!$B$2:$B$200, A2, Claims!$G$2:$G$200, "Settled") | =B2-C2 |
| Spouse | ₹5,00,000 | =SUMIFS(Claims!$F$2:$F$200, Claims!$B$2:$B$200, A3, Claims!$G$2:$G$200, "Settled") | =B3-C3 |
The argument order in SUMIFS is: the range to add, then each range and its condition. It works the same in Excel and Google Sheets.
Does a floater change the sum?
Yes. In a floater the cover is shared, so the base figure is entered once and every member's claims count against it. Say the floater is ₹10,00,000 and settled claims so far are ₹1,20,000 for one member and ₹85,000 for another. Total settled is ₹2,05,000, so the balance is ₹10,00,000 minus ₹2,05,000, which is ₹7,95,000. For that case, drop the Member condition from SUMIFS and add everything with status Settled.
Which date starts the year?
A policy year runs from your policy start date, not from April. A claim belongs to the policy year in which the admission happened, so add a Policy year column and filter on it. When the policy renews, start a new block or tab for the new year.
What about claims that are still pending?
Do not subtract them from the balance, but do not forget them either. Add a second formula showing the amount claimed on pending rows:
- =SUMIFS(Claims!$E$2:$E$200, Claims!$G$2:$G$200, "Pending") gives the total waiting to be settled.
- Subtract that pending total from the balance in column D to see the worst case, if everything pending were paid in full.
How do I keep the insurer's own statement beside the sheet?
Ask the insurer, through its app, helpline or branch, how you can get a statement of cover used and cover remaining for the current policy year. Save each copy with the date you received it, in the same folder as the policy. Once a quarter, or after any big claim, add a line under the member table: Insurer balance on this date, then the figure, then the difference from your sheet.
A difference is not an accident to hide. It is a clue. Often it is a pending claim, a co-payment you forgot, or a bonus the insurer has added. Write the reason beside the difference, so next year you know how your policy behaves.
When a sheet stops being enough
If the bills, claim letters and payments pile up faster than you can update the tabs, SpendixAI keeps receipts with their transactions and lets you add the hospital and medicine spends as expenses with your own categories. It does not read insurer balances, so the cover sheet remains a separate record.
A quick end-of-claim routine
- Enter the claim row as soon as it is intimated, status Pending.
- Change it to Settled with the amount in the letter, once paid.
- Compare the sheet's balance with the insurer's, and note the date you checked.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

