• Summarizing entries in a spreadsheet


    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

    Viewing 2 reply threads
    • #2324502

      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

    • #2324503

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

      cheers, Paul

    • #2324521

      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.


    Viewing 2 reply threads
    Reply To: Summarizing entries in a spreadsheet

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: