Summarizing entries in a spreadsheet

Topic

New Reply

I need a bit of help in summarizing/extracting data from a spreadsheet.  The spreadsheet contains financial data. One of the columns contains the date in the format YYYY-MM-DD, another column contains a transaction type (ALPHA data, e.g., Fee) and another column contains the cost of the transaction, e.g., 195.43 without a $.  For each month, there are five types of the same transaction.  This transaction type is “Fee” for a month for each of five accounts.

What I want to produce is a summary table by month for the fees for the five accounts.  I do not want each account summarized, that may come in future requirements.  For now, all I want is a summary total for each month for all five accounts.

I can probably use a pivot table for this, if I can just figure out how to structure the pivot table (help or suggestions, please), but I am curious to find out if there is another way to do this using SUMIFS and/or other functions available in Excel.

Any insight that anyone would care to provide will be greatly appreciated. Thanks.

===========================

Ron M

Reply | Quote

Replies

First, is the date column actually a date or text? If it’s text you will need to convert it to the equivalent number.

I think you need a helper column with =MONTH(A1) to use the month as the criteria for either a Pivot or SUMIFS.
If you are doing it by year as well, the helper would be:
=0+(YEAR(A1)&TEXT(MONTH(A1), "00"))

You need the date in the format “YYYYMM” for this to work, but concatenating (&) converts to text, as does the TEXT function that gives us 2 digit months, so you need to add the whole lot to a number (0) to convert it back to a number.

cheers, Paul

Reply | Quote

p.s. You may be able to do the whole lot by formatting the date as YYYYMM, not tested that.

cheers, Paul

Reply | Quote

Paul T, thanks for the help.  Yes, the Date column is actually a date.  I will try creating a new column called month and get it from the date.  I think I can then use a Pivot table with month as one of the rows.

Ron

Reply | Quote