Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup nightmere (2003)

    Hi,

    I am in a real mess here now! I am trying to get a table to count the number of a coloum where the next coloum = a set day of week

    i.e.

    In the cell coloum Mon and row 1 I want it to lookup all the matching data from the table to the left of the sheet and give me a count of patient group where patient group = 1 and DOW = Mon

    Many thanks

    I have attached a cut down sample
    Attached Files Attached Files
    Regards
    Gerbil (AKA Kevin)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup nightmere (2003)

    Enter this formula in F2:

    =SUMPRODUCT(($A$2:$A$21=$E2)*(LEFT($B$2:$B$21,3)=L EFT(F$1,3)))

    Fill down to F11, then fill right to column L.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup nightmere (2003)

    Hi Hans,

    Thanks for that, it worked great but... I forogt to mention something...

    The is an extra column in the data table called breach, I only want them previous request to be counted if the value in column breach is equal to 1

    Many thanks
    Regards
    Gerbil (AKA Kevin)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup nightmere (2003)

    You should be able to extend the formula to include the extra condition.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup nightmere (2003)

    Sorry Hans

    I'm been at bit stupid, but I cant get the extra condition to work

    Can you help, many thanks
    Regards
    Gerbil (AKA Kevin)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup nightmere (2003)

    There's no column called Breach in the sample workbook that you posted... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  7. #7
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup nightmere (2003)

    Hi Hans, was just about to upload a new copy of the spreadsheet and the answer came to me!!

    =SUMPRODUCT(($A$2:$A$21=$F2)*(LEFT($B$2:$B$21,3)=L EFT(G$1,3)),($D$2:$D$21=$E2)*(LEFT($B$2:$B$21,3)=L EFT(G$1,3)))

    Many thanks
    Regards
    Gerbil (AKA Kevin)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup nightmere (2003)

    You don't have to repeat the condition LEFT($B$2:$B$21,3)=LEFT(G$1,3):

    =SUMPRODUCT(($A$2:$A$21=$F2)*(LEFT($B$2:$B$21,3)=L EFT(G$1,3))*($D$2:$D$21=$E2))

  9. #9
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup nightmere (2003)

    Thank Hans,

    How do I get this same idea to work but give an average.

    Thanks
    Regards
    Gerbil (AKA Kevin)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup nightmere (2003)

    The general idea is

    =AVERAGE(IF((condition1)*(condition2)*(condition3) ,data))

    as an array formula, i.e. confirm the formula with Ctrl+Shift+Enter. The number of conditions can vary.

  11. #11
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup nightmere (2003)

    Hi thanks hans, that works great.

    On some of the cells I get #DIV/0! where there is no data in the source table. The data changes daily so the forumla needs to always be there but is there a way of making #DIV/0!
    display as -

    maybe somesort of error handler

    Thanks
    Regards
    Gerbil (AKA Kevin)

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup nightmere (2003)

    In general, if you have a formula
    <code>
    =EXPRESSION
    </code>
    that might return an error value, you can suppress it completely by changing the formula to
    <code>
    =IF(ISERROR(EXPRESSION),"",EXPRESSION)
    </code>
    or you can display a custom value:
    <code>
    =IF(ISERROR(EXPRESSION),"-",EXPRESSION)</code>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •