+ Reply to Thread
Results 1 to 3 of 3

Excel Formula for sum dates in monthly wise.

  1. #1
    Registered User
    Join Date
    11-25-2021
    Location
    India
    MS-Off Ver
    2016
    Posts
    54

    Excel Formula for sum dates in monthly wise.

    I have data in date wise which excel formula I use that separate date by monthly and sum total value.
    Month Report.xlsx
    Last edited by meraz; 01-23-2023 at 04:33 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Excel Formula for sum dates in monthly wise.

    Put the date of the first day of the month (i.e. 1/04/2022) into the merged cell K3/K4, then you can use this formula in cell L5:

    =SUMIFS(INDEX(Table1[[Place 1]:[Place 5]],,MATCH($K5,Table1[[#Headers],[Place 1]:[Place 5]],0)),Table1[Mode Type],L$4,Table1[Date],">="&$K$3,Table1[Date],"<="&EOMONTH($K$3,0))

    Copy this across into M5, then copy those two formulae down to complete the first table.

    You can use the same formula for the lower tables, but you must first put the date in K13/K14 (i.e. 1/05/2022), and then copy the formula from L5 into cell L15 and change the references shown in red above so that the formula looks at the dates in $K$13, then copy across and down as above. Repeat this for the other tables.

    Hope this helps.

    Pete

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Excel Formula for sum dates in monthly wise.

    Another

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    =SUMPRODUCT((Table1[[Place 1]:[Place 5]])*(Table1[Mode Type]=L$4)*(Table1[Date]>=$K$2)*(Table1[Date]<=EOMONTH($K$2,0))*(Table1[[#Headers],[Place 1]:[Place 5]]=$K5))[/FORMULA]

    As per Pete's post: put month in K2 and change formula above for other months
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. [SOLVED] Formula to calculate the number of days between dates (month wise)
    By arun.sj in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-22-2019, 12:51 PM
  2. VBA to extract monthly wise transactions & format
    By MaheShetty in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-03-2018, 03:29 AM
  3. [SOLVED] How get sales data between dates and item wise customer wise
    By devawad in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-17-2017, 05:54 AM
  4. [SOLVED] need to arrange column wise data to row wise with monthly wise
    By alok.gupta4ever in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-03-2016, 11:11 AM
  5. [SOLVED] Cumulative monthly count needed for department wise
    By kittu55 in forum Excel General
    Replies: 3
    Last Post: 08-19-2015, 08:47 PM
  6. [SOLVED] I want change the data row wise to coloum wise & coloumn wise to row wise.
    By satputenandkumar0 in forum Excel General
    Replies: 3
    Last Post: 12-20-2012, 08:34 AM
  7. Delete row wise duplicates & colomun wise simultaneously excel
    By Dipankar in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-06-2005, 09:05 AM

Tags for this Thread

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