LAMBDA lets you turn a formula into your own function, so you can write a calculation once and call it by name with different inputs. In an expense sheet, a good first one is NetSpend: debits minus refunds for a category in a month. LAMBDA is available in Microsoft 365; Excel 2021 and older do not have it.
What do we want the function to do?
Say your transactions are in a sheet with Date in A, Category in B, Type in C (either Debit or Refund) and Amount in D, for rows 2 to 500. You often need: for a given category and a given month, how much did I really spend after refunds? Today you would write two SUMIFS and subtract. That is exactly what we will wrap up.
Step 1: write the formula you already know
For Food in April 2026, the plain version is:
=SUMIFS($D$2:$D$500,$B$2:$B$500,"Food",$C$2:$C$500,"Debit",$A$2:$A$500,">="&DATE(2026,4,1),$A$2:$A$500,"<"&EDATE(DATE(2026,4,1),1)) - SUMIFS($D$2:$D$500,$B$2:$B$500,"Food",$C$2:$C$500,"Refund",$A$2:$A$500,">="&DATE(2026,4,1),$A$2:$A$500,"<"&EDATE(DATE(2026,4,1),1))
The EDATE part gives the first day of the next month, so the window is April 1 up to but not including May 1. This catches every day of the month without worrying whether it has 28, 30 or 31 days.
Step 2: turn it into a LAMBDA
LAMBDA takes its inputs first, then the calculation: LAMBDA(input1, input2, calculation). The two inputs are the category and the first day of the month. We also use LET so the shared date window is written once:
=LAMBDA(cat,start,LET(nxt,EDATE(start,1),SUMIFS($D$2:$D$500,$B$2:$B$500,cat,$C$2:$C$500,"Debit",$A$2:$A$500,">="&start,$A$2:$A$500,"<"&nxt)-SUMIFS($D$2:$D$500,$B$2:$B$500,cat,$C$2:$C$500,"Refund",$A$2:$A$500,">="&start,$A$2:$A$500,"<"&nxt)))
Typed alone in a cell, this gives a #CALC! error, because nothing has told it what to calculate yet. That is normal.
Step 3: test it in a cell
Call it right away by adding the inputs in brackets after the closing bracket of the LAMBDA: ("Food",DATE(2026,4,1)). So the cell holds the whole LAMBDA followed by ("Food",DATE(2026,4,1)). With some hypothetical data, April Food debits are ₹8,400 and ₹5,800, and one refund of ₹650. The answer should be 8,400 + 5,800 - 650 = ₹13,550.
| Date | Category | Type | Amount |
|---|---|---|---|
| 4 Apr | Food | Debit | ₹8,400 |
| 12 Apr | Food | Debit | ₹5,800 |
| 19 Apr | Food | Refund | ₹650 |
| Net Food, April | - | - | ₹13,550 |
Step 4: save it under a name
- Copy the LAMBDA formula from your test cell, leaving off the test inputs at the end.
- Go to the Formulas tab and open Name Manager.
- Choose New. In Name, type NetSpend.
- In Refers to, paste the LAMBDA, with an equals sign at the start so it reads =LAMBDA(cat,start,...).
- Click OK, then in any cell type =NetSpend("Food",DATE(2026,4,1)).
Now =NetSpend("Travel",DATE(2026,5,1)) gives travel for May with no further typing. Put the month starts in a row and the categories in a column and you can build a whole grid by pointing at those cells.
Use it in a grid
The payoff comes when you reuse the name. Put category names down column A, starting in A2, and the first day of each month across row 1, starting in B1. Then in B2 type =NetSpend($A2,B$1) and fill it right and down. Every cell calls the same function with its own category and month, and a mistake fixed inside the saved name is fixed everywhere at once. With about eight categories and twelve months, that is 96 cells from one definition.
What can go wrong?
- #NAME? means the name is spelled differently from the one in Name Manager, or the file is opened in a version without LAMBDA.
- #CALC! in the cell where you typed the bare LAMBDA means it has no inputs yet.
- A refund recorded as a negative debit will be subtracted twice. Decide your convention and stick to it.
- Workbooks using named LAMBDAs will not calculate for anyone on Excel 2021 or older.
When is a custom function worth it?
If you copy the same long formula in many cells, a name saves time and removes slips. If you use the formula once, leave it as it is. A custom function is also easy to change in one place, which is its biggest advantage over a formula pasted into hundreds of cells. Keep a note in the sheet that says what NetSpend does and which columns it expects, because you will forget in six months.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


