+ Reply to Thread
Results 1 to 8 of 8

Do-Until Loop with multiple condition

  1. #1
    Registered User
    Join Date
    10-05-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2007
    Posts
    37

    Do-Until Loop with multiple condition

    Hi all

    Yesterday I wrote one hell of a long IF-statement (the whole statement falls in the range or A1:U31 with font size set to 11) and only after that I read about the Do-Until Loop. However, I am still not sure how to use this function.

    The criteria as as follows:

    - IF(AND(S12=DATE(2012,1,1),S14="VIP",S16="LS"),SUMIFS(AC:AC,Y:Y,DATE(2012,1,1),AA:AA,"VIP",AB:AB,"LS") OR

    - IF(AND(S12=DATE(2012,1,1),S14="Life",S16="LS"),SUMIFS(AC:AC,Y:Y,DATE(2012,1,1),AA:AA,"Life",AB:AB,"LS") OR

    - IF(AND(S12=DATE(2012,1,1),S14="VIP",S16="PR"),SUMIFS(AC:AC,Y:Y,DATE(2012,1,1),AA:AA,"VIP",AB:AB,"PR") OR

    - IF(AND(S12=DATE(2012,1,1),S14="Life",S16="PR"),SUMIFS(AC:AC,Y:Y,DATE(2012,1,1),AA:AA,"Life",AB:AB,"PR") OR

    - IF(AND(S12=DATE(2012,1,1),S14="RA",S16="LS"),SUMIFS(AC:AC,Y:Y,DATE(2012,1,1),AA:AA,"RA",AB:AB,"LS")



    The above are the search criteria for January -- I need the loop to do the search for the whole year (i.e. January to December).

    I will really appreciate any help and/or advice.

    Thanks guys!
    Cheers
    Last edited by bf0109; 12-16-2012 at 09:57 AM.

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Do-Until Loop with multiple condition

    Have you tried using a Pivot Table - seems a viable choice for this and no need for VBA or nested IF formulas.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  3. #3
    Registered User
    Join Date
    10-05-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Do-Until Loop with multiple condition

    Palmetto

    Thanks for your reply. I actually thought about a pivot table, but I don't know whether it will fit into the design I have planned.

    See the file attached.

    The whole calculation should be in Cell F27
    Attached Files Attached Files

  4. #4
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Do-Until Loop with multiple condition

    WOW! What an eye-chart that IF formula is. I don't have the time or the inclination to wade through every part of that formual so I will reply with what I perceive as the general approach to use.

    Personally, I prefer to keep raw data on a separate sheet and reporting on another. It is a better way to organize the workbook and allows room on the data sheet ( or use a thrird sheet) to make further manipulations/calculations for reporting purposes.

    You have a lot of hardcoded values in your SUMIFS formula.
    SUMIFS(P:P,L:L,DATE(2012,1,1),N:N,"VIP",O:O,"LS")

    Could be written as:
    SUMIFS(P:P,L:L,$F$21,N:N,$F$23,O:O,$F$25)
    You could, possibly, use this one formula for all of the variants you now have. Or you may need one or two variants at most.

    In conjunction with the suggested formula, consider using a drop down list in each of the variable cells (F23 and F25).
    I would also convert the data range into an Excel Table so that it become a dynamic range. Then, instead of referencing entire columns (i.e. P:P), we could use structured references that only takes in the actual number of rows & columns.

    More on Structured References Here

    Hope that Helps.
    Last edited by Palmetto; 12-16-2012 at 10:05 AM. Reason: correct typo

  5. #5
    Registered User
    Join Date
    10-05-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Do-Until Loop with multiple condition

    Awesome!! Makes life a whole lot easier. I didn't even know you can insert such a table.

    Thank you Palmetto!

  6. #6
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Do-Until Loop with multiple condition

    Glad to be of help.

  7. #7
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Do-Until Loop with multiple condition

    This post is in reply to your PM.

    To use a drop down list using Data Validation see this page.

  8. #8
    Registered User
    Join Date
    10-05-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Do-Until Loop with multiple condition

    Thanks again, Palmetto.

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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