+ Reply to Thread
Results 1 to 7 of 7

Total amount if month is januari

  1. #1
    Registered User
    Join Date
    01-16-2015
    Location
    Maaseik (Bel)
    MS-Off Ver
    2013
    Posts
    6

    Total amount if month is januari

    Hello,

    I have a table with some colums.
    A1 to A10 : date (normal date format xx/xx/xxxx)
    E1 to E10 : amount

    For K5 I need a formula that makes a total amount for the records with a date in januari.

    Many thanks.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Total amount if month is januari

    Hi

    One way

    With January 1st in say F1

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Or you could use a pivot table
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    01-16-2015
    Location
    Maaseik (Bel)
    MS-Off Ver
    2013
    Posts
    6

    Re: Total amount if month is januari

    Thanks for the quick answer.
    Why should I add a second colum with the date ?
    In the A colum are the dates.
    Maybe I wrote it wrong, but what I need is a formula for cell K5 that makes the total of the amounts in colum E if the date in colum A is in januari.

    See picture.
    example.jpg
    Last edited by Octonet; 01-16-2015 at 09:33 PM.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Total amount if month is januari

    I dont think Richard is suggesting you have a new column, he is saying that he put your criteria date in F1 - you could put the date anywhere, just adjust the forumla accordingly
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    01-16-2015
    Location
    Maaseik (Bel)
    MS-Off Ver
    2013
    Posts
    6

    Re: Total amount if month is januari

    Solution found with a matrix formula.
    There is a compatibility problem in Exell entering an english formula into an excel sheet of another language.
    For the Dutch Exell this would be :
    =SOM((maand(A2:A100)=1)*(B2:B100)) : It makes the sum of values in comumn B where the month is january in comumn A.
    For English version of Excel it could be : =SUM((month(A2:A100)=1)*(B2:B100))

    Because this is a matrix formula : do not enter the formula just with "enter". Enter it by pressing "CTRL/SHIFT/ENTER"

    It works like a charm.

    Question : is there a solution to make Excel 2013 of different languages compatible for entering formulas ? In the far past this was no problem.

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Total amount if month is januari

    @Octonet

    Another solution => use an pivot table.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Total amount if month is januari

    @Octonet,

    Try SUMPRODUCT and Enter only:

    =SUMPRODUCT((MONTH(A2:A100)=1)*E2:E100)

    Or SUMIFS:

    =SUMIFS(E2:E100,A2:A100,"<="&"31/01/2015",A2:A100,">="&"01/01/2015")
    Quang PT

+ 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. Total amount is month is januari
    By Octonet in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-16-2015, 07:53 PM
  2. I am trying to get total amount by month
    By amm_manaz in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-06-2014, 11:54 AM
  3. 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
  4. [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
  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