+ Reply to Thread
Results 1 to 6 of 6

Get Total Sum Based On Dates

  1. 04-14-2021, 11:01 AM #1
    vill
    vill is offline
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Pro Plus 2016
    Posts
    301

    Get Total Sum Based On Dates

    Hi guys, this one is beyond my capabilities... some help please?

    As per screenshot, I have a range in yellow where I want to get total sum of all income for that specific month.

    In Data tab, we have dates and balances entered.

    What would be the way to get those totals in Activity tab, next to each Month (where the first earliest date entered in Data tab would be Mo1, and following dates would fall into Mo2, Mo3 etc cells)

    Attaching Excel as well. Without arrays please.

    Thanking in advance!!!

    https://www.excelforum.com/attachmen...1&d=1618412421


    2021-04-14_17-55-17.png
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-14-2021, 11:38 AM #2
    JohnTopley
    JohnTopley is offline
    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,169

    Re: Get Total Sum Based On Dates

    Try

    in D34

    =SUMIFS(Data!$BV$11:$BV$15,Data!$BU$11:$BU$15,">=" &D$33,Data!$BU$11:$BU$15,"<=" &EOMONTH(D$33,0))

    Replace MO1 etc with proper dates e.g 01/02/2021., 01/03/2021
    Reply With QuoteRegister To Reply
  3. 04-14-2021, 11:43 AM #3
    vill
    vill is offline
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Pro Plus 2016
    Posts
    301

    Re: Get Total Sum Based On Dates

    Thanks, John! And without replacing MO1 etc with proper dates, not possible? That was the whole point, cannot have dates in there...
    Reply With QuoteRegister To Reply
  4. 04-14-2021, 11:50 AM #4
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Get Total Sum Based On Dates

    Please try at D35

    =SUMIFS(Data!$BV$11:$BV$19,Data!$BU$11:$BU$19,">"&EOMONTH(MIN(Data!$BU$11:$BU$19),MID(D33,3,2)-2),Data!$BU$11:$BU$19,"<="&EOMONTH(MIN(Data!$BU$11:$BU$19),MID(D33,3,2)-1))
    Attached Files Attached Files
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  5. 04-14-2021, 11:51 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,647

    Re: Get Total Sum Based On Dates

    Instead of Mo1, why not have the dates copied across from the data tab? That would resolve the issue.

    How are the dates presented on the data tab? You could create named ranges called Mo1, etc.and then use INDIRECT, but seems a faff.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  6. 04-14-2021, 11:51 AM #6
    vill
    vill is offline
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Pro Plus 2016
    Posts
    301

    Re: Get Total Sum Based On Dates

    Magic, thank you Bo_Ry!!! Greatly appreciate your help, guys.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sum the total number of times a word appears in a column based on dates
    By Cremorneguy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-26-2015, 05:47 AM
  2. [SOLVED] Formula to calculate the total # of days between all dates in Col A and Col B based on...
    By Stanimal in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 12-08-2014, 10:21 PM
  3. [SOLVED] Running total based on dates in a year
    By JillJ in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-11-2013, 08:45 AM
  4. [SOLVED] Formula for calculating total in a row based on dates in another row
    By srmp07 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-15-2013, 03:05 PM
  5. Excel 2007 : Mileage total based on different dates and different vehicles
    By Not You in forum Excel General
    Replies: 3
    Last Post: 03-21-2012, 11:40 PM
  6. Excel 2007 : Mileage total based on different dates
    By Not You in forum Excel General
    Replies: 6
    Last Post: 02-05-2012, 03:01 PM
  7. Formula to calculate a total in one range based on dates from another column
    By CarolineD in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-01-2005, 04:56 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1