Results 1 to 7 of 7

Thread: Creating a category (text) with a number value and summing it

  1. #1

    Question Creating a category (text) with a number value and summing it



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi everyone,

    I have two sheets that look like:


    Sheet1
    A B C
    1 Date Amount Category
    2 18/10/12 15,00 Food
    3 19/10/12 40,00 Car
    4 19/10/12 15,00 Tools
    5 20/10/12 5,00 Food

    Sheet2
    A B C
    1 Month Amount Category
    2 Oct-12 20,00 Food
    3 Oct-12 40,00 Car
    4 Oct-12 15,00 Tools

    I'm trying to make cells B2 to B4 return the total of each category (B2+B5 in sheet1 = B2 in sheet2, in the example above).
    Since the list is very large and will continue growing, I need to find a way to automate this calculation.
    The way I've tried to approach this is:

    Based on SUMIF( range, criteria, [sum_range] )
    I'm trying =SUMIF((Sheet1 column C), (Food), B:B)


    where "Food" is a name defined for each category, but I don't know how I could match automatically cells B2 and B5 to C2 and C5.
    I simply need Excel to find and sum all of the expenses within a category, in a selected month.

    Any help or alternative ideas will be greatly appreciated.

    Thanks.


  2. #2
    Put this formula in B2 Sheet2

    =SUMIF(Sheet1!$C$2:$C$5,"Food",Sheet1!$B$2:$B$5)

    copy down and change name of criteria (Food) in B3 and B4

  3. #3
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    You could just replace "Food" in that formula to C2

    =SUMIF(Sheet1!$C$2:$C$5,C2,Sheet1!$B$2:$B$5)

    Then when you copy it down it will automatically look for the value in column C of the same row.

  4. #4

    Smile Looking good!

    Thanks for the fast and effective reply. Both formulas worked, although I preferred the second one. I will just point out that since I'm using Excel 2010, I had to replace the commas with semicolons. This is the current formula for the cells in column C:

    SUMIF(Sheet1!$C$2:$C$5;C2;Sheet1!$B$2:$B$5)

    But I'd like to take it one step further. The idea is to select the date range and get the sum and categories for that period.
    So if I select the cells A3 to A6 on Table1 below, column B on Table 2 would display the total spent in each category.

    Table1
    A B C
    1 Date Amount Category
    2 30/09/12 15 Phone
    3 02/10/12 18 Food
    4 05/10/12 40 Car
    5 09/10/12 5 Office
    6 18/10/12 20 Food
    7 01/11/12 30 Movies


    Table2 (displaying only expenses in October)
    A B C
    1 Month Amount Category
    2 02/10/12 38 Food
    3 18/10/12 40 Car
    4 5 Office

    Some notes about these tables:
    1. In Table1 entries are sorted from oldest to newest, but in Table2, sorting doesn't matter.
    2. The 'Month' column would contain the earliest and latest date from the selected month.
    3. There would be a new table for each month.
    4. Columns B and C on Table2 should be automatically filled once selected the date range.

    This is what I think the formula should do, but I don't know how to "translate" it:
    SUMIF((I select A3:A6 on Table1);(Excel chooses one category within the date range);(Sums values for selected category))

    Any ideas how to do this?

    Thanks.

  5. #5
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    I can help with the sorting it out by month, I am unsure of how to display the first entry of the month and the last.

    If you add a new column on Sheet1, Column D, and just label it as month.
    You can parse out the month from the date you have in column A by using this formula. =MONTH(A2)
    that will just give you the number value of the month from column A.

    On Sheet2 you will need to add another column, Column D, I labeled it as month to check. then in cell D2 just put the month you want in your example 10.
    Your formula in B2 should now look like. =SUMIFS(Sheet1!$B$2:$B$13,Sheet1!$C$2:$C$13,C2,Sheet1!$D$2:$D$13,$D$2) replace the , with ;
    Then you can just change the month to search for in cell D2.

    As a side note, you may need to tweak the dates to return the correct values. I had to enter the date as month/day/year on my end to get my functions to work.
    If you are using a European version that has day/month/year it may be ok.

    I have attached a file showing this working.
    Megalodon.xlsx

    Simi

  6. #6
    Here is one way you could fill in the month on Sheet2. I just updated Simi's file
    Attached Files Attached Files

  7. #7
    Amazing! So kind of you guys. Works like a charm.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •