+ Reply to Thread
Results 1 to 11 of 11

Summing data based on the week and today

  1. #1
    Registered User
    Join Date
    02-04-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    48

    Summing data based on the week and today

    Hi guys,

    I have attached the excel reference, please note that the total tables are examples based on the days. The actual data should just have one total table that updates based on the week and dates.

    I basically want to sum up the data in this week (including today) and it should only include this week's data.

    Thanks very much for you guy's help!

    example.xlsx

  2. #2
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Summing data based on the week and today

    It is a little unclear what you are trying to accomplish, but hopefully the attached file is close.
    Attached Files Attached Files
    Please click the * icon below if I have helped.

  3. #3
    Forum Contributor thameem127's Avatar
    Join Date
    04-06-2012
    Location
    Jeddah,Saudi Arabia
    MS-Off Ver
    Excel 2003,Excel 2007
    Posts
    321

    Re: Summing data based on the week and today

    sumif.xlsx

    Check this file.

  4. #4
    Registered User
    Join Date
    02-04-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Summing data based on the week and today

    Thank you guys for the help. I am sorry to confuse you guys, but here is a newer file that should make it clearer. And I believe we need some sort of checking of today's date and which week is it to determine what is the range we want to sum up.

    example.xlsx

  5. #5
    Registered User
    Join Date
    02-04-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Summing data based on the week and today

    Quote Originally Posted by Melvinrobb View Post
    It is a little unclear what you are trying to accomplish, but hopefully the attached file is close.
    Quote Originally Posted by thameem127 View Post
    Attachment 298229

    Check this file.

    Thank you guys for the help. I am sorry to confuse you guys, but here is a newer file that should make it clearer. And I believe we need some sort of checking of today's date and which week is it to determine what is the range we want to sum up.

    Attachment 298235

  6. #6
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Summing data based on the week and today

    Take a look at the attached sheet. if it isn't what you are looking for, be specific about what instances are not what you are looking for, and why.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-04-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Summing data based on the week and today

    Quote Originally Posted by Melvinrobb View Post
    Take a look at the attached sheet. if it isn't what you are looking for, be specific about what instances are not what you are looking for, and why.
    Here, I have attached the actual file. Sorry again, I was trying to simplify things but I guess it confused everyone

    example.xlsx

  8. #8
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Summing data based on the week and today

    Cell E17 is empty. is this because there was no number for January 13th? is there anyway that instead of that cell being blank, it could show "0"?
    That way, if the cell in column E has a value in it, you would include that in the Target #. The formula below would work for that purpose (paste in J2 and drag down/right):

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

  9. #9
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Summing data based on the week and today

    Hi
    the formula below does not considers today"s target and sums whats up to date. Then takes daily target and sums actual date. For example:
    Until today 3 days sales: 15000 + 4*3000(target) = 27000

    =ROUND(IF(COUNTIF(OFFSET($C$1,0,0,MATCH($L$1,A:A,0)-1,1),I2)=7,SUMIF(C:C,I2,E:E),SUMIF(C:C,I2,E:E)+(J2/7*(7-COUNTIF(OFFSET($C$1,0,0,MATCH($L$1,A:A,0)-1,1),I2)))),1)

    Uploaded the file
    Appreciate the help? CLICK *

  10. #10
    Registered User
    Join Date
    02-04-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Summing data based on the week and today

    Quote Originally Posted by Melvinrobb View Post
    Cell E17 is empty. is this because there was no number for January 13th? is there anyway that instead of that cell being blank, it could show "0"?
    That way, if the cell in column E has a value in it, you would include that in the Target #. The formula below would work for that purpose (paste in J2 and drag down/right):

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

    I think this formula works perfectly. Thanks so much for your help and I am so sorry for all the confusion!!!

  11. #11
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Summing data based on the week and today

    It will not work if January 13th is blank, as the target amount for January 13th is not included in the sum.
    You will need to have the cell show "0" instead of blank to make the sum accurate.

+ 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] Summing Data by Week Ending and Category
    By cpalmer72 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-12-2013, 11:13 PM
  2. Summing Data by Week Ending and Category
    By cpalmer72 in forum Excel General
    Replies: 1
    Last Post: 10-03-2013, 02:30 PM
  3. Macro worked last week, won't work today!?
    By Jackdaddy0711 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-09-2012, 06:13 PM
  4. Today's week number
    By kizzie in forum Excel General
    Replies: 5
    Last Post: 12-08-2006, 02:34 AM
  5. [SOLVED] Summing or avg moving ranges based on week and day???
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-28-2006, 04:55 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1