+ Reply to Thread
Results 1 to 8 of 8

I am trying to get total amount by month

  1. #1
    Registered User
    Join Date
    02-03-2014
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    4

    I am trying to get total amount by month

    I have done a shopping cost sheet, which obviously has the date column in DD/MM/YY format. I have "Purchase Item type" and "Amount" column as well, I have a separate table to get Monthly Cost by Purchase Item Type.

    I am not sure how to get the Total Amount by Purchase Item Type by the Month;
    Eg How much I have spend in Groceries, Vegetables, etc.

    Which formula I could use?

    I have got the whole total amount till January to now by this formula;
    =SUMIFS(Table1[[#All],[Amount]],Table1[[#All],[Purchase Item Type]],[@[Purchase Item Type]])
    Need further help; thanks

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,339

    Re: I am trying to get total amount by month

    I'd suggest using Pivot Table instead of formula in such case.
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    02-03-2014
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: I am trying to get total amount by month

    Thanks, I have tried, yet its working. Still; what could be the formula to get (Condtion) to get the detail. If I do not want to use pivot table, but yet if i want to get the same result, what could be sumif condition i need to use.

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,339

    Re: I am trying to get total amount by month

    try to use two more conditions in your sumifs
    for instance for October 2013:
    Table1[[#All],[Purchase Date]],"<" & DATE(2013,11,1)
    and
    Table1[[#All],[Purchase Date]],">=" & DATE(2013,10,1)

    so the whole formula would be
    =SUMIFS(Table1[[#All],[Amount]],Table1[[#All],[Purchase Item Type]],[@[Purchase Item Type]],Table1[[#All],[Purchase Date]],"<" & DATE(2013,11,1),Table1[[#All],[Purchase Date]],">=" & DATE(2013,10,1))

    of course date(s) can be stored in some cell.
    some further functions to be included in the formula could be TODAY() and EOMONTH

  5. #5
    Registered User
    Join Date
    02-03-2014
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: I am trying to get total amount by month

    Thanks Kaper, I have tried the same code, as I have done some adjustment. Its not showing in the following table which I have created. its only showing as '0' values in . My date (You put as Purchase Date) "Date" as it is in the following format; "01/01/14", And I started my table with the starting date from this year January.not sure what is causing the trouble. I have attached the sheet for you to checkit out. I am trying to get the whole basics, rather doing everything very simple and easy as using pivot table. Thanks you help me out with the pivot table. as I already created and found it very much easy.

    Please help me out with it. Many thanks
    Untitled-1.gif

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,339

    Re: I am trying to get total amount by month

    Honestly, I'd strongly recommend reading forum rules and focusing on "Want to get your question answered quickly?" section.

    Have a good day,

    Kaper
    Last edited by Kaper; 02-06-2014 at 02:18 AM. Reason: :lol: was rorum instead of forum - probably shall install kinda spellchecker

  7. #7
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,340

    Re: I am trying to get total amount by month

    @amm_manaz Welcome to the forum. What Kaper is saying..."post a real workbook" we don't want Kaper or any other member who want's to help you to redo the workbook based on your picture so..

    To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  8. #8
    Registered User
    Join Date
    02-03-2014
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: I am trying to get total amount by month

    Thanks a lot guys, I have tried this code; and it worked.

    =SUMIFS(Table1[[#All],[Amount]],Table1[[#All],[Purchase Item Type]],[@[Purchase Item Type]],Table1[[#All],[Date]],">=1/1/2014",Table1[[#All],[Date]],"<=31/1/2014")

    Thanks to Kaper; I am going to use pivot table which is make the job much easier. I have learn something on the ways; thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. how to work out rate based on total time and total amount
    By lisaathers in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-09-2013, 06:44 AM
  2. [SOLVED] Returning total amount earned in a month by day of the week
    By klowned in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-02-2012, 01:03 AM
  3. Validation by Total Amount
    By Power Penguin in forum Excel General
    Replies: 2
    Last Post: 09-11-2006, 11:03 AM
  4. Replies: 1
    Last Post: 07-27-2005, 09:36 PM
  5. [SOLVED] How do I calculate Amount of Sales Tax from Total Amount?
    By MikeS in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-26-2005, 04:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1