+ Reply to Thread
Results 1 to 27 of 27

Simplying SUMIF formula

  1. #1
    Registered User
    Join Date
    08-18-2017
    Location
    Leicester, England
    MS-Off Ver
    Office 2013 & 365
    Posts
    54

    Simplying SUMIF formula

    Hi

    I am creating a workbook for supplier order schedule monitored and forecasted on a weekly basis and currently using this formula:


    =SUMIF($L$1,"<="&$E$2,N5)+SUMIF($O$1,"<="&$E$2,Q5)+SUMIF($R$1,"<="&$E$2,T5)+SUMIF($U$1,"<="&$E$2,W5)+SUMIF($X$1,"<="&$E$2,Z5)+SUMIF($AA$1,"<="&$E$2,AC5)+SUMIF($AD$1,"<="&$E$2,AF5)+SUMIF($AG$1,"<="&$E$2,AI5)+SUMIF($AJ$1,"<="&$E$2,AL5)+SUMIF($AM$1,"<="&$E$2,AO5)+SUMIF($AP$1,"<="&$E$2,AR5)+SUMIF($AS$1,"<="&$E$2,AU5)+SUMIF($AV$1,"<="&$E$2,AX5)+SUMIF($AY$1,"<="&$E$2,BA5)+SUMIF($BB$1,"<="&$E$2,BD5)+SUMIF($BE$1,"<="&$E$2,BG5)+SUMIF($BH$1,"<="&$E$2,BJ5)+SUMIF($BK$1,"<="&$E$2,BM5)+SUMIF($BN$1,"<="&$E$2,BP5)+SUMIF($BQ$1,"<="&$E$2,BS5)+SUMIF($BT$1,"<="&$E$2,BV5)

    I am having to manually add cells to the formula when new data is added.
    L1, O1, R1, etc., (i.e. every 4th column) is the WEEKNUM.
    E2 is WEEKNUM(TODAY)
    N5, Q5, T5 etc., (again every 4th column) is the balance of any outstanding order
    I have attached a sample. Would appreciate any help in automating the formula
    Attached Files Attached Files

  2. #2
    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
    79,401

    Re: Simplying SUMIF formula

    1. What, in WORDS, is the formula doing? Be exact, please.
    2. What are the expected results?
    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.

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Simplying SUMIF formula

    Try this

    =SUMIF($L$1:$BT$1,"<="&$E$2,$N5:$BV5)

  4. #4
    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: Simplying SUMIF formula

    Can I respectfully suggest that you are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

    A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it.

    You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
    You will also throw open the whole wonderful world of the powerful Pivot table functionality.

    So before you get too far with this I'd create a single sheet database that contains the following columns

    Date
    Supplier
    Cast No.
    Finished Part No.
    Record Type ' i.e. entries from a drop down cell would be either 'SCH' or 'REC'
    Value

    And then base any analysis on this table. The advantage of a Pivot Table as well as the ability to view things in diferrent ways is that it performs the equivalent of Lookups and SUMIFS/COUNTIFS but without the need to create any formulae.



    The * values could be Validation drop down cells from which you could pick values. The way I normally arrange these things is to have a single data entry row above the database in which the new values are entered, then a button which runs a macro that adds the new record to the database.
    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.

  5. #5
    Registered User
    Join Date
    08-18-2017
    Location
    Leicester, England
    MS-Off Ver
    Office 2013 & 365
    Posts
    54

    Re: Simplying SUMIF formula

    Thank you for the suggestion, this seems to work perfectly. Thank you so much!!!

  6. #6
    Registered User
    Join Date
    08-18-2017
    Location
    Leicester, England
    MS-Off Ver
    Office 2013 & 365
    Posts
    54

    Re: Simplying SUMIF formula

    AliGW: I thought sending an attachment would better explain what I am trying to achieve than explaining in WORDS, so apologies if it wasn't as obvious as I thought it would be. But I believe Jonmo1 has solved it.

  7. #7
    Registered User
    Join Date
    08-18-2017
    Location
    Leicester, England
    MS-Off Ver
    Office 2013 & 365
    Posts
    54

    Re: Simplying SUMIF formula

    I will certainly try your suggestion at some point, however, I am too far into this and the worksheet is being used by management to monitor the orders with suppliers but I will certainly have a play and see if this works for me. Thank you again.

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Simplying SUMIF formula

    You’re welcome

  9. #9
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Simplying SUMIF formula

    in addition to Jonmo1's suggestion you could also go for a SUMIFS formula that gives you a little more control to also show the totals of the other two columns

    for 2017
    Please Login or Register  to view this content.

  10. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Simplying SUMIF formula

    @Roel

    We need to sum the column that is 2 to the right from the column that has the <E2 criteria.
    If L1 < E2, sum N5
    If M1 < E2, sum O5

  11. #11
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Simplying SUMIF formula

    That is what it does. Did you get a wrong result using this formula? or what is the problem with it?

    oh wait.. I see the problem, got tricked by merged cells again..
    nevermind my answer then, does not work in this case.
    Last edited by Roel Jongman; 04-07-2018 at 04:50 PM.

  12. #12
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Simplying SUMIF formula

    Because of the merged cells in Row 1 (say L1:N1)
    Only the top/left cell of a merged range contains a value.
    So only L1 contains a numeric value, M1 and N1 are empty.
    So N1<E2 will never be true. Therefore N5 will never be summed.

    You would need to offset the Row3 Criteria ("Bal") and the Sum Row5 to columns from the Row1 Criteria (<E2)

    =SUMIFS($N5:$BV5,$L$1:$BT$1,"<"&$E$2,$N$3:$BV$3,"BAL")


    But that 2nd criteria isn't necessary. N5 will be summed based on the value in L1 meeting the <E2 criteria.

  13. #13
    Registered User
    Join Date
    08-18-2017
    Location
    Leicester, England
    MS-Off Ver
    Office 2013 & 365
    Posts
    54

    Re: Simplying SUMIF formula

    Hi Richard

    You posted a suggestion some time ago for my supplier scheduling. At the time I was already too far into the project and had a deadline so couldn't work on your suggestion. The task of supplier scheduling will now be passed on to me to manage. I have created a table as per your suggestion and a simple pivot table . However, I need to be able to identify if the schedule delivery is late for supplier adherence and I'm not sure how to do this. I wondered whether it would be better to create rec'pt date and quantity as columns rather than drop down cells and add a formula to identify arrears. Would appreciate your advice.
    Attached Files Attached Files
    Last edited by Amita68; 07-11-2018 at 03:15 AM. Reason: Replaced workbook with correct one.

  14. #14
    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
    79,401

    Re: Simplying SUMIF formula

    I have removed the solved tag to draw attention back to this thread.

  15. #15
    Registered User
    Join Date
    08-18-2017
    Location
    Leicester, England
    MS-Off Ver
    Office 2013 & 365
    Posts
    54

    Re: Simplying SUMIF formula

    Thank you AliGW

  16. #16
    Registered User
    Join Date
    08-18-2017
    Location
    Leicester, England
    MS-Off Ver
    Office 2013 & 365
    Posts
    54

    Re: Simplying SUMIF formula

    AliGW
    I'm not getting a response so I will start a new thread more appropriate to my new request rather than continuing with this old one. I will mark this as solved.

  17. #17
    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
    79,401

    Re: Simplying SUMIF formula

    No, you won't - sorry. Duplicate threads are not allowed - just bump this thread with more information, please. You can update this thread's title if you wish.
    Last edited by AliGW; 07-16-2018 at 05:11 AM.

  18. #18
    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: Simplying SUMIF formula

    Would you indicate what answers you expect for the example data you show.
    Specifically how are you defining 'late' and what you want to show and where i.e. the result layout.

    Add the results manually to the workbook, or mock up an area that contains the results.

  19. #19
    Registered User
    Join Date
    08-18-2017
    Location
    Leicester, England
    MS-Off Ver
    Office 2013 & 365
    Posts
    54

    Re: Simplying SUMIF formula

    Hello Richard
    Thank you for replying. I have since posting, been working on my sheet and made a few more changes, please see updated sheet.
    This is a supplier schedule and I need to record requirements and receipts and monitor their adherence to schedule. I'm not sure whether what I have done so far is the correct approach, but I'm stuck
    on a formula that needs to display the week in which arrears exist, (the point being to fulfil outstanding requirements first and monitor number of weeks late/early). Please see comments on the sheet.
    Attached Files Attached Files

  20. #20
    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: Simplying SUMIF formula

    You'll appreciate that whilst you're entirely familiar with your data, how you use it and what you expect any results to look like, that I'm start.ing from scratch.

    Terms like 'adherence to schedule' mean something to you since you know what and where the schedule data is and what YOU mean by adherence, but to those of us who don't know your business or terminology it's difficult to know exactly what you expect.

    You need to hold our hands and manually add all the results you expect to see and ensure that all permutations of data are present along with the results they produce. Cleatly identify which is original data and which information is the results that are derived.

  21. #21
    Registered User
    Join Date
    08-18-2017
    Location
    Leicester, England
    MS-Off Ver
    Office 2013 & 365
    Posts
    54

    Re: Simplying SUMIF formula

    Richard
    Apologies, I did add comments to the sheets but I obviously didn't attached the correct workbook. Please see updated attachment. Hope it makes sense.
    Attached Files Attached Files

  22. #22
    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: Simplying SUMIF formula

    Can you give me some examples of the values you expect to see in column K for a few of the castings and explain the calculation you make to arrive at the value. Make sure there are examples of different permutations of Week No, Receipts and Balance.

  23. #23
    Registered User
    Join Date
    08-18-2017
    Location
    Leicester, England
    MS-Off Ver
    Office 2013 & 365
    Posts
    54

    Re: Simplying SUMIF formula

    Please see highlighted cells displaying expected results and the comments in these cells. I'm stuck on the formula to display the oldest week number with an outstanding balance. I presumed it would be the same as column J which is currently :

    =SUMIFS(TBCastSch[[Sch Bal ]],TBCastSch[Casting No],[@[Casting No]],TBCastSch[Wk No.],"<"&[@[Wk No. Rec''d]])

    but would need to incorporate the MIN and IF formulae into it. I don't know how to do this.
    Attached Files Attached Files

  24. #24
    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: Simplying SUMIF formula

    Does the attached offer a way forward?

    I created a helper column K on the Casting Sch sheet and a temporary column N on the Casting Recepts sheet which you can compare with the results you indicate in column K. Assuming column N is correct then this formula should be put in column K.

    I wasn't sure about K40 since rows 39 & 40 appear to be a duplicate and I'm assuming there should not be a row 40.
    Attached Files Attached Files

  25. #25
    Registered User
    Join Date
    08-18-2017
    Location
    Leicester, England
    MS-Off Ver
    Office 2013 & 365
    Posts
    54

    Re: Simplying SUMIF formula

    Thank you for this. I'm not confident with formulae so I'm still trying to work out what the formulae is doing.

    I eventually figured out what Col K is doing, but I don't see where it is referred to in either sheet, confused, sorry.

    To clarify row 39 and 40, the supplier has delivered a total of 800 castings on the same delivery note, 500 of which should have been delivered in wk26 (2wks late = -2 in Col L) and 300 against a schedule quantity of 500 earlier than required for wk30 (2wks early = 2 in Col L).

    Looking at this example, row39, as there is no live schedule requirement for wk28 (the week in which it was rec'd) the test column correctly shows wk26. However, if you look in the Casting Sch sheet, the receipt of this 500 for wk26 has not been recorded because the formula in Rec'pts (Col H) is referring to Casting Rec'pts sht, Col B. I adjusted the formula to refer to Col N, but got #Value errors.

    But I will work on this, thank you for your help.

  26. #26
    Registered User
    Join Date
    08-18-2017
    Location
    Leicester, England
    MS-Off Ver
    Office 2013 & 365
    Posts
    54

    Re: Simplying SUMIF formula

    Just writing to update: I solved my issue in displaying the oldest week number with an outstanding schedule. As suspected I did need the MIN and IF formulae to achieve this, however my problem was that I was using the column that converted the date from another column into the week number and year displayed as "WK-YEAR". I adjusted the formula to look at the date column instead and it worked fine.

  27. #27
    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
    79,401

    Re: Simplying SUMIF formula

    Thanks for sharing - a very useful postscript to the thread.

+ 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. Replies: 1
    Last Post: 01-22-2012, 02:47 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