Help require with excel formulas

Copper Contributor

I have a spreadsheet I have downloaded.


It is a report showing the individual card payments taken over a period of a year.


It has 17000 rows and 2 columns

First column has dates 

Second comumn has amounts


I need to be able to get daily totals of the card payments taken over that year. One day might have 30 card transactions and another might have 5.


I have tried a pivot table it only showed me monthly yearly and quarterly totals and I need daily.


Any help greatly appreciated 

3 Replies


To calculate daily totals of card payments from your report, you can use the SUMIFS formula in Excel. Here is how you can do it:

Assuming your dates are in column A and amounts are in column B, and your data starts from row 2, you can follow these steps:

  1. Insert a new column next to the "Amounts" column (let's say it's column C) and enter the formula below in the first cell (cell C2):


This formula sums the amounts in column B where the date in column A matches the date in the current row.

  1. Drag the formula down to apply it to all rows in column C.
  2. Now, column C will show the daily totals of card payments corresponding to each date.

Alternatively, if you prefer a pivot table to summarize the data, you can follow these steps:

  1. Select your data range (both columns) including headers.
  2. Go to the "Insert" tab in the Excel ribbon.
  3. Click on "PivotTable" and choose the location where you want to place the pivot table.
  4. In the PivotTable Field List, drag the "Dates" field to the "Rows" area, and the "Amounts" field to the "Values" area.
  5. By default, the pivot table will show the sum of amounts by dates. Right-click on any date in the pivot table, select "Group", and choose "Days" to group the dates by days.
  6. Now, the pivot table will display the daily totals of card payments.

These methods should help you obtain the daily totals of card payments from your report. Choose the one that suits your preference and reporting needs.

If you need more help, please include the following info to help others answer your question:

Welcome to your Excel discussion space!

You can use a PivotTable. Note that the default behavior of a Pivot is to auto-group the dates to cleanup the row labels. Right-click a date and choose to ungroup to get desired appearance.


An alternative to a pivot is to use a formula like this:

    uDate, UNIQUE(date),
    totals, SUMIF(date, uDate, payment),
    HSTACK(uDate, totals)