Your total income says how you are doing. Income per client says how fragile that is. If you freelance, two spreadsheet formulas and one check row show both, and they work the same in Excel and Google Sheets.
The data
One row per payment, with a Type column ("Income" or "Expense"), a Client column and an Amount column. Here is a small example, with Type in column B, Client in E and Amount in F on a sheet called Transactions:
| Type | Client | Amount |
|---|---|---|
| Income | Northwind | 4200 |
| Income | Northwind | 1800 |
| Income | Fabrikam | 900 |
| Income | Contoso | 1300 |
| Expense | (blank) | 40 |
| Income | Northwind Cafe | 600 |
| Income | Fabrikam | 700 |
Total income is 9,500. Keep that number in mind.
Step 1: income per client
On a second sheet, list the client names in column A from row 2 down. In B2:
=SUMIFS(Transactions!$F:$F, Transactions!$B:$B, "Income", Transactions!$E:$E, $A2)
Copy it down. To limit it to one year, add two more conditions on the date column.
Step 2: share of total
In C2:
=IF(SUM($B$2:$B$30)=0, 0, B2/SUM($B$2:$B$30))
Format the column as a percentage. The IF stops a divide-by-zero error before you have logged any income. Sort the table by income, largest first.
Step 3: the check row
This is the part people skip. Put this in E2:
=SUMIFS(Transactions!$F:$F, Transactions!$B:$B, "Income") - SUM(B2:B30)
It should read 0. I ran the example above with three clients listed (Northwind, Fabrikam, Contoso) and recalculated it in LibreOffice:
| Client | Income | Share |
|---|---|---|
| Northwind | 6,000 | 67.4% |
| Fabrikam | 1,600 | 18.0% |
| Contoso | 1,300 | 14.6% |
The check cell showed 600, which is the "Northwind Cafe" row. SUMIFS matches the text exactly, so "Northwind" and "Northwind Cafe" are two different clients, and the second one was never listed.
The check matters for a second reason. The share column divides by the sum of the clients you listed, not by your real total. With the 600 missing, Northwind shows 67.4%, but against the real 9,500 it is 63.2%. A missing client inflates every other share, so fix the check before you trust the percentages.
Keeping it at zero
- Add a new client to the list on the same day you log their first payment.
- Use a dropdown (data validation) on the Client column instead of typing names freehand. "northwind cafe" and "Northwind Cafe" then cannot drift apart.
- If the check is not zero, filter the Transactions sheet by Income and look for names that are not on your summary list.
Reading the result
There is no universal safe percentage. The point is that you can now see your own number and decide whether you are comfortable with it. One client with a large share means a single late payment or lost contract hurts a lot. Many small clients usually means more admin per dollar, which you can compare against how many invoices each one takes. Run the same formula for each quarter side by side and a shrinking client shows up.
This is general spreadsheet technique, not financial or tax advice. The full walkthrough, with the other tracker formulas, is here: https://sheets.hexloomlabs.com/articles/track-freelance-income-by-client-spreadsheet
Top comments (0)