A multi-rate invoice sheet needs one row per item with its own GST percentage, and a summary that adds taxable value and tax separately for each rate using SUMIFS. Line total is =Taxable+GST amount, and the grand total of the lines should match the grand total of the summary. A small shop invoice with items at two rates shows how.
The item table
- Row 4 headings: A No, B Item, C Qty, D Unit price, E Taxable value, F GST %, G GST amount, H Line total.
- E5: =C5*D5.
- F5: the rate for that item, chosen from a dropdown list so it is always a proper percentage.
- G5: =ROUND(E5*F5, 2).
- H5: =E5+G5.
- Copy the formulas down to row 24 so there is space for twenty items, then total E, G and H in row 25 with =SUM.
A worked shop invoice
Say a stationery shop bills these items, with rates taken from its own price list (the two rates here are illustrations only):
| Item | Qty x price | Taxable | GST % | GST | Line total |
|---|---|---|---|---|---|
| Notebooks (pack of 5) | 10 x ₹45.00 | ₹450.00 | 12% | ₹54.00 | ₹504.00 |
| Gel pens (box) | 5 x ₹120.00 | ₹600.00 | 18% | ₹108.00 | ₹708.00 |
| Box files | 4 x ₹85.00 | ₹340.00 | 12% | ₹40.80 | ₹380.80 |
| Markers (set) | 6 x ₹60.00 | ₹360.00 | 18% | ₹64.80 | ₹424.80 |
The invoice total is ₹2,017.60.
How does the SUMIFS summary by rate work?
Build a small block to the right, say in J4:L7, with each rate on its own row. SUMIFS takes the range to add first, then the range to test, then the value to match.
- J5: 12% and J6: 18%, the rates you use.
- K5: =SUMIFS($E$5:$E$24, $F$5:$F$24, J5) gives the taxable value at that rate.
- L5: =SUMIFS($G$5:$G$24, $F$5:$F$24, J5) gives the GST at that rate.
- Copy K5:L5 down, and total both columns in row 7.
| Rate | Taxable value | GST | Taxable + GST |
|---|---|---|---|
| 12% | ₹790.00 | ₹94.80 | ₹884.80 |
| 18% | ₹960.00 | ₹172.80 | ₹1,132.80 |
| Total | ₹1,750.00 | ₹267.60 | ₹2,017.60 |
How do you read one row?
Take the first line: 10 notebooks at ₹45 give a taxable value of ₹450.00. At 12%, GST is 450 x 0.12 = ₹54.00 and the line total is ₹504.00. The second line is 5 x ₹120 = ₹600.00, GST at 18% is ₹108.00, line total ₹708.00. If a discount column is needed, change E5 to =C5*D5*(1-I5), where I5 holds the discount percentage, and leave the rest alone.
Why did a rate not match in SUMIFS?
SUMIFS compares values exactly. A rate that looks like 12% but was produced by a calculation, such as 0.06+0.06, can differ from the typed 0.12 in the last decimal places and be skipped. Type the rates, or pick them from the dropdown, rather than calculating them.
Can I make it print like an invoice?
- Put the seller details, invoice number and date above the table, and the customer details beside them.
- Use Page Layout, Print Area to select only the invoice block.
- Hide the helper columns or leave them outside the print area.
- Save the file under the invoice number so earlier bills are never overwritten.
What this sheet does not do
It does not know which rate applies to an item, whether tax is CGST and SGST or IGST, or what must appear on a valid tax invoice. Those depend on rules that change, so confirm the format and rates with your CA or the official source. If you also want to see your own purchases by GST rate, SpendixAI categories can carry a GST rate and its reports give an estimate to compare with your bills.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


