+ Reply to Thread
Results 1 to 17 of 17

SUMIF formula help

  1. #1
    Registered User
    Join Date
    11-03-2015
    Location
    San Antonio, TX
    MS-Off Ver
    Excel 2010
    Posts
    63

    Question SUMIF formula help

    I need help writing a SUMIF formula if a number is present more than thirty days ago based on today's date.

    I have a SS where I want to see this sum on the first tab. The first tab also has a cell (C2) that has the formula =TODAY() to reflect today's date no matter when the Spreadsheet is opened. On the second tab starting in column B, every third column is labeled THREE (which is the difference between columns ONE & TWO).
    I want to sum the values in columns labeled "THREE", only if the date above "THREE" is more than 30 days ago from today.

    I prefer the dates are listed horizontally as opposed to vertically. I don't want to filter anything.

    11
    B C D
    1 Sun Sun Sun
    2 10/25 10/25 10/25
    3 ONE TWO THREE
    4 120 123 3
    Last edited by jnswbc; 11-11-2015 at 06:10 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: SUMIF formula help

    only if the date above "THREE" is more than 30 days from today.
    I assume you mean more than 30 days into the future. Your sample dates are in the past so I'm not so sure.

    I also assume that the values you want to sum are in row 4.

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


    Replace Sheet2 with the actual name on the second tab. I have not tested this but would be happy to do so if you attach your file.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    11-03-2015
    Location
    San Antonio, TX
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: SUMIF formula help

    I actually do mean 30 days ago, looking into the past, not the future. I have attached my sample for your viewing pleasure...

    TESTER 2.xlsx

    I am looking for a sum in column J on the WOH tab only if there are numerical values greater than zero on the Completed Work tab under the "INCOMP" columns if the date above (same color as the background) that INCOMP column is more than 30 days prior todays date (WOH!C2).
    Attached Files Attached Files

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: SUMIF formula help

    This is a bit different than the example you gave in your first post (always better to attach the sample file right straight away). First, the date is not immediately above the INCOMP column header. Second, you want to sum several rows of numbers under each INCOM column, not just one number. Will have to rethink.

  5. #5
    Registered User
    Join Date
    11-03-2015
    Location
    San Antonio, TX
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: SUMIF formula help

    There is a date immediately above the INCOMP column header... Just as there is one above RECVD and COMP. However, you won't "see" the date because its the same color as the background color. I'm not sure why there are two attachments in my last reply, however, if you click the first one (60.7 KB) within the text, perhaps you will see what I am talking about?

    Thank you for your input as well. I typically include my attachment when I post. The one time I don't results in understandable confusion!

  6. #6
    Registered User
    Join Date
    11-03-2015
    Location
    San Antonio, TX
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: SUMIF formula help

    I was researching SUMIFS formulas thinking the following would work, but it doesn't. I basically need a formula for a 30 day period, 7 day and 1 day based on the frequency of my reports in Column B on the WOH tab. I tried to create a formula based on your suggestions and my research for the report on line 3 of the WOH tab with a daily (1 day) frequency.

    Please Login or Register  to view this content.
    Also tried:
    Please Login or Register  to view this content.
    As well as:
    Please Login or Register  to view this content.
    Last edited by jnswbc; 11-11-2015 at 06:43 PM.

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: SUMIF formula help

    Quote Originally Posted by jnswbc View Post
    There is a date immediately above the INCOMP column header... Just as there is one above RECVD and COMP. However, you won't "see" the date because its the same color as the background color.
    Sorry but that's not what is there. The date is only above the COMP columns. Formula box is blank:
    blankcell.png
    Another tip: For the day of the week, reference the date immediately below but format as Ddd. That will guarantee that the right dates are matched with the right days.

    Looking at WOH, it looks like each row corresponds to a row in Completed Work. So I'm going to assume that for each row in WOH, you want the sum of the number in the corresponding row of Completed Work that fall into the INCOMP column with days older than 30 days prior to today. My original formula was the right idea. Here it is implemented in your file, but I did have to add the dates. I formatted them as you had fantasized

    However, I couldn't really test it. You don't have any dates on Completed Work that are older than 30 days.

    The formulas you tried have some problems in terms of how you used quotes.

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

    This formula will look for a cell that has a value of "=TODAY()-1" and "=INCOMP". What you want is
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This will match only yesterday's date.


    In SUMIF and SUMIFS, the criteria value is a value or condition that must match values in the row or column to qualify to be included in the sum. Unfortunately, Microsoft has not done a good job of documenting exactly what options are available to use here.

    If you just want to match a specific value, just use the value. It can be a number, string, or cell reference:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    But you can also include comparisons. The way to do that is to build a string beginning with > < >= <=. (I have never tried to use just = but it's not necessary.)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    will match all positive values. To build a string with a function or cell reference, use concatenation. This will match dates that are 30 days or more older than today:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-03-2015
    Location
    San Antonio, TX
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: SUMIF formula help

    Thank you so so much for breaking this down little by little! I appreciate that a lot!

    I have a question...

    What is the difference between the formula you provided above
    Please Login or Register  to view this content.
    and the formula you provided in the attachment (aside from the 30 and 1 values in regards to today's date)?
    Please Login or Register  to view this content.
    Is it just the way the range is referenced? Do they do the same thing?

  9. #9
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: SUMIF formula help

    In the first formula, the sum_range specifies a range of columns, whereas in the second formula I used the entire row. Insulates against changes in what columns you use.

    Both formulas provide two pairs of criteria and criteria_range, but their order is switched. This doesn't matter. (First one is date then INCOMP, second one is INCOMP then date.)

    The date criteria in the second formula uses cell $C$2 instead of TODAY(). Because C2 contains TODAY(), the result would be the same.

    The criteria in the first formula is TODAY()-1. This means that the date must exactly match TODAY()-1. In the second formula, the criteria includes "less than" and is "<"&$C$2-30, which means that the date must be earlier than 30 days prior to today.

  10. #10
    Registered User
    Join Date
    11-03-2015
    Location
    San Antonio, TX
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: SUMIF formula help

    Awesome, thank you for breaking it down for an amateur like me!

  11. #11
    Registered User
    Join Date
    11-03-2015
    Location
    San Antonio, TX
    MS-Off Ver
    Excel 2010
    Posts
    63

    Question Re: SUMIF formula help

    Ok, I have a few questions... I have also attached my sample workbook.

    I tried using the formula you provided for WOH!column J from your attachment, but it wouldn't work on my end. So I used the one provided in your reply on the message board, which seemed to have worked for the first report I have listed on WOH for one day. However, it will not work for 7 days or 30 days prior (the formulas listed in WOH!J4:J5, which should currently reflect a value of 100). I also made sure to have sample data for more than 7 & 30 days ago inputted on the Completed Work tab.

    [code]=SUMIFS('Completed Work'!B4:HC4,'Completed Work'!B2:HC2,TODAY()-1,'Completed Work'!B3:HC3,"INCOMP")[\code]

    Please advise what I am doing wrong above!

    Also...
    Would you mind looking at the formula in WOH!C30:I30? It should be pulling the data from Completed Work, line 31.

    And...
    Would you mind looking at the formula in WOH!L3:L28? It should be calculating a rolling two week average. However, the average 2 week average I get (when I manually calculate it) for line 1 & 2 is 1507.1443 & 1000 respectively.

    TEST 1112.xlsx

  12. #12
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: SUMIF formula help

    I tried using the formula you provided for WOH!column J from your attachment, but it wouldn't work on my end. So I used the one provided in your reply on the message board, which seemed to have worked for the first report I have listed on WOH for one day. However, it will not work for 7 days or 30 days prior (the formulas listed in WOH!J4:J5, which should currently reflect a value of 100). I also made sure to have sample data for more than 7 & 30 days ago inputted on the Completed Work tab.
    I don't understand where you need results for 1 or 7 days. Your original post referred to 30 days, and there is only one column to put this number in. I revised the formulas in column J to look for dates earlier than 30 days ago. You only have one date that is older than 30 days, which is 10/4. There is only one row that has data under INCOMP on that day and it is "Ocwen – Timing Cycle Accuracy (#1)". The sum in column J is calculating correctly.

    Would you mind looking at the formula in WOH!C30:I30? It should be pulling the data from Completed Work, line 31.
    Fixed. You need to use the fourth parameter for HLOOKUP, which you omitted.

    Would you mind looking at the formula in WOH!L3:L28? It should be calculating a rolling two week average. However, the average 2 week average I get (when I manually calculate it) for line 1 & 2 is 1507.1443 & 1000 respectively.
    How are you calculating by hand? When I calculate by hand, I get numbers that exactly match the formulas. It is looking at two weeks, that is, 14 days, starting from today and working backwards.

    I also froze panes so I could scroll and still see headings. Remove it if you don't like it.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    11-03-2015
    Location
    San Antonio, TX
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: SUMIF formula help

    I don't understand where you need results for 1 or 7 days.
    In the sample I provided on the WOH tab, in column B, I have 3 different frequencies listed. Daily (1), Weekly (7), & Monthly (roughly 30). The formulas in column J will depend on the frequency in column B, because I'm looking for column J to tell me if the reports I have listed in column A are outside of my frequency range (listed in column B). I only asked about the 30 day formula, because I assumed it would be as easy as replacing the value 30 with 7 or 1 as needed.

    Adjusting what you provided in your most recent attachment did the trick! THANK YOU MUCH!!!

    How are you calculating by hand? When I calculate by hand, I get numbers that exactly match the formulas. It is looking at two weeks, that is, 14 days, starting from today and working backwards.
    I was adding the COMP cells starting in 11/11 and going 13 days back, dividing by 14. You can see my "calculations in AY40:41 & BB40:41.

    I also froze panes so I could scroll and still see headings. Remove it if you don't like it.
    I prefer the frozen panes, but I couldn't figure out how to make more than the first column or row stick.


    Thank you so much for letting me pick your Excel brain!
    Last edited by jnswbc; 11-12-2015 at 05:44 PM.

  14. #14
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: SUMIF formula help

    Quote Originally Posted by jnswbc View Post
    ...I assumed it would be as easy as replacing the value 30 with 7 or 1 as needed.
    Kind of. For example, for 7, do you want the sum of all values greater than 7 days ago, or greater than 7 but less than 30? If you want all values greater than 7 days ago, then yes, just substitute the desired number for 7.


    I was adding the COMP cells starting in 11/11 and going 13 days back, dividing by 14.
    Today is 11/12.

  15. #15
    Registered User
    Join Date
    11-03-2015
    Location
    San Antonio, TX
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: SUMIF formula help

    ...do you want the sum of all values greater than 7 days ago...?
    Yes! I adjusted the formula you provided, and it works with my sample data!

    Can you tell me how you calculated it by hand...? /facepalm

  16. #16
    Registered User
    Join Date
    11-03-2015
    Location
    San Antonio, TX
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: SUMIF formula help

    Nevermind, I figured it out! lol. Thank you so much for your assistance!

  17. #17
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: SUMIF formula help

    The average? Summed up 14 INCOMP values starting with 11/12 and going backwards then divided by 14.

+ 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] My formula sumif/indirect formula is working perfect..except everything is 1 row off!
    By Nyolls in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-23-2015, 01:12 PM
  2. SumIf formula or Sumif with pivottable combined question
    By mcgleeuw in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-10-2015, 01:05 PM
  3. Replies: 2
    Last Post: 09-16-2014, 10:13 AM
  4. [SOLVED] Shorten SUMIF + SUMIF + SUMIF each with same reference range but different criteria???
    By HeyInKy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-13-2014, 09:25 PM
  5. SUMIF or SUMIF's formula with multiple criteria
    By jackiemariee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-30-2013, 06:19 PM
  6. SumIF formula with criteria is Text inside a Formula?
    By excellentlearner in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-25-2012, 06:40 PM
  7. SUMIF formula that is collects values from an IF formula
    By stanja in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-26-2009, 02:37 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