# How to calculate the net of credits and debits in Excel

Subtract one SUMIF from the other: `=SUMIF(B2:B8,"Credit",C2:C8)-SUMIF(B2:B8,"Debit",C2:C8)`.

**Excel:** `=SUMIF(B2:B8,"Credit",C2:C8)-SUMIF(B2:B8,"Debit",C2:C8)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Date | Type | Amount |  | Net |
| 2026-03-01 | Credit | 5200 |  |  |
| 2026-03-03 | Debit | 1480 |  |  |
| 2026-03-08 | Debit | 312.5 |  |  |
| 2026-03-12 | Credit | 875 |  |  |
| 2026-03-19 | Debit | 2260 |  |  |
| 2026-03-24 | Credit | 1430 |  |  |
| 2026-03-30 | Debit | 96.25 |  |  |

Formula in F1: `=SUMIF(B2:B8,"Credit",C2:C8)-SUMIF(B2:B8,"Debit",C2:C8)`

Result: $3,356.25

## How it works

The amounts are all positive and the Type column says which way the money went. One SUMIF adds the credits (7,505), the other adds the debits (4,148.75), and subtracting gives the net change for the period.

## Other ways

- `=SUMPRODUCT(((B2:B8="Credit")-(B2:B8="Debit"))*C2:C8)`: Gives each row a sign (+1 or -1) from its type and adds the signed amounts in one pass.

## Common mistakes

- `=SUM(C2:C8)` adds debits as if they were income. With unsigned amounts, the type column decides the sign.
- A type typed differently ("debit ", "DR") falls out of both totals. Use a dropdown for the Type column.

## Questions

**How do I show a running balance?**

Fill `=SUMPRODUCT((($B$2:B2="Credit")-($B$2:B2="Debit"))*$C$2:C2)` down a column.

**How do I turn the amounts into signed numbers?**

Use `=IF(B2="Debit",-C2,C2)`; then a plain SUM gives the net.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/net-of-credits-and-debits