+ Reply to Thread
Results 1 to 10 of 10

Complex multi criteria lookup and count formula???

  1. #1
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Complex multi criteria lookup and count formula???

    I need a lookup formula or something similar to display data in "table 3" in Sheet 2 when I enter the date in the highlighted box. Each date contains different data symbols in "table 1" on sheet 1 and the value of each symbol is in "table 2" in cells C13 to D19 on sheet 1.
    Anyway, I have a little bit more explaination in the book "Demo Data" below.

    TIA
    Attached Files Attached Files
    Last edited by JapanDave; 05-21-2009 at 12:05 PM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Complex multi criteria lookup and count formula???

    To avoid need for repetitive calcs I would advise the following:

    Please Login or Register  to view this content.
    To then populate results:

    Please Login or Register  to view this content.

  3. #3
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Complex multi criteria lookup and count formula???

    Quote Originally Posted by DonkeyOte View Post
    To avoid need for repetitive calcs I would advise the following:

    Please Login or Register  to view this content.
    To then populate results:

    Please Login or Register  to view this content.
    Thanks for the really quick reply.

    What sheet are you saying to enter the formula for C4 and D4? If I was to enter them in Sheet 1 I would lose the letters for those cells?

    Edit: I just figured out it was for sheet 2! Thanks so much again for all the help DonkeyOte!!!
    Last edited by JapanDave; 05-21-2009 at 12:05 PM.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Complex multi criteria lookup and count formula???

    Everything to be added to Sheet2... C4 & D4 just reduce the number of repetitive calcs you have to perform, repetitive calcs are obviously to the detriment of overall performance.

    On the subject of performance, word of warning, Arrays used en masse should generally be avoided... in your sample there aren't too many but be wary of overusing.

  5. #5
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Complex multi criteria lookup and count formula???

    Thanks I will keep that in mind, as I have them on other books and some of them take up to 30 seconds to open up on a PC with quad core extreme and 4 gig of memory! I should start streamlining them for sure.

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Complex multi criteria lookup and count formula???

    Yes that sounds bad... I've helped a few people here with poor array performance - I recall one in particular going from 40 min calc time to around 10 secs, achieved by simply foregoing elegance for the sake of efficiency... do post up any significant problematic/bottlenecks if you want assistance in reworking... arrays are great in Moderation (this includes Sumproduct).

    Generally speaking IMO Concatenation is the key to avoiding Arrays, eg:

    A1:A1000 contains strings
    B1:B1000 contains numbers
    C1:C1000 contains strings
    D1:D1000 contains numbers

    Let's say we wanted to sum D where A began with the letter A, B was > 10 and C ended with "xy".... a typical approach would be:

    =SUMPRODUCT(--(LEFT(A1:A1000,1)="A"),--(B1:B1000>10),--(RIGHT(C1:C1000,2)="xy"),D1:D1000)

    On it's own this is fine but if we assumed we doing lots of these for various combinations of strings in A&C etc things would get very expensive very quickly...

    A simple, far less elegant but far more efficient approach is to use concatenation, eg we add a concatenation column at source - merging the columns of interest:

    E1: =A1&":"&(B1>10)&":"&C1
    copied to E1000 - for our number test we use a Boolean return rather than the value

    Simply by adding E we no longer need to use SUMPRODUCT, we can use the far more efficient SUMIF with Wildcards in our criteria string so as to handle our string tests of A & C etc...

    =SUMIF(E1:E1000,"A*:TRUE:*xy",D1:D1000)

    As I say just a quick example.

    (For those ready to jump in with 2007 SUMIFS, yes I know, the above is an illustration of a concept )

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Complex multi criteria lookup and count formula???

    and a very nice illustration, DonkeyOte, I might add
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  8. #8
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Complex multi criteria lookup and count formula???

    Thanks DonkeyOte!
    I must admit I am having trouble understanding your simple examples! LOL
    I don't know why some of those symbols are in the equations and the only way to find out is study harder. The problem I am having is that I am on Japanese 2007 and although the equations are the same all the wording is different. Layout is basically the same so I can get an understanding of the english used (Even though the name says Dave, Japanese is my stronger language at this point, but it is not posing a problem)

    Is there anywhere I can go (on the net) to learn some of the functions you are stating? Excel is very misunderstood here and there are not many people who are good at it, let alone classes to attend.

    Anyway, as always thanks so much for the help as it is making my life much easier.

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Complex multi criteria lookup and count formula???

    Quote Originally Posted by JapanDave
    Is there anywhere I can go (on the net) to learn some of the functions you are stating?
    To be honest I picked up my knowledge from sites such as these, colleagues and XL Help... others may have suggestions ?

    Quote Originally Posted by JapanDave
    Excel is very misunderstood here and there are not many people who are good at it, let alone classes to attend
    Hmm, maybe I should relocate to Japan

  10. #10
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Complex multi criteria lookup and count formula???

    Quote Originally Posted by DonkeyOte View Post



    Hmm, maybe I should relocate to Japan
    You would do well here I would say!!!

+ 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