Use SUMIF in Excel to Total Transactions by Credit Card
Question details
Calculate the total transaction amount for each credit card using a conditional sum.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook contains transaction amounts and credit card names on a transaction sheet, with card names listed on a summary sheet.
- Observed behavior
- Each card on the summary sheet should show the sum of its matching transaction amounts.
Identify the transaction sheet, the column containing card names, and the column containing numeric amounts. On your summary sheet, list each card name exactly as it appears in the transaction data.
Sum transactions for each credit card with SUMIF
Use this formula when each transaction has a card name and an amount in separate columns.
The example assumes card names are in column B and amounts are in column C of a sheet named Transactions. The card to match is in cell A2 of the summary sheet.
On the summary sheet, click cell B2 next to the card name in A2.
Type =SUMIF(Transactions!$B:$B,A2,Transactions!$C:$C) in B2 and press Enter. Replace the sheet name and column references if your workbook uses different locations.
Select B2 and drag its fill handle down beside the remaining card names. The A2 reference changes for each row, while the transaction columns stay fixed.
Total credit card transactions in WPS Spreadsheets
Open your workbook in WPS Spreadsheets and use the same SUMIF formula to summarize transaction amounts by card.
- 1. Open the workbook: In WPS Spreadsheets, use Open to select your Excel workbook.
- 2. List the cards: On a summary sheet, enter each card name in column A, starting at A2.
- 3. Add the formula: Click B2 and enter =SUMIF(Transactions!$B:$B,A2,Transactions!$C:$C). Adjust the sheet and column references to match your workbook, then press Enter.
- 4. Fill the totals down: Select B2 and drag the fill handle down alongside the other card names.
Frequently Asked Questions
What does each part of the SUMIF formula mean?
In =SUMIF(Transactions!$B:$B,A2,Transactions!$C:$C), column B contains the card names to check, A2 contains the card name to match, and column C contains the amounts to add.
How do I use a transaction sheet whose name contains spaces?
Put single quotes around the sheet name in the formula. For example, use =SUMIF('Card Transactions'!$B:$B,A2,'Card Transactions'!$C:$C).
Why does my card total show zero?
Check that the name in the summary cell matches the transaction entries and that the amount column contains numbers rather than text. Also verify the sheet and column references in the formula.
Will refunds be included in the total?
SUMIF adds all matching numeric amounts as recorded. If refunds are entered as negative amounts, they reduce the card total.
