Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a formula that is working, but need a copy that will only sum the values for a PROJECTED DAYS field when the hospital number starts with 031

    =SUMPRODUCT(--(INDIRECT(Detail_Whse_Region)=$B77),
    --(INDIRECT(Detail_Whse_Class)<>"O"),
    --(INDIRECT(Detail_Whse_Hospital)="031*"),
    --INDIRECT(Detail_Proj_Days))

    The example above uses the * like you would for a wildcard query (in Access, etc), but that functionality apparently does not work within a formula. I use this indirect method all over this spreadsheet to do various "queries" - as the data table is rather cumbersome (4000 rows X 71 columns) - but this wildcard query has me stumped.

    Thanks in advance,
    Bill

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Try this,

    =SUMPRODUCT(--(INDIRECT(Detail_Whse_Region)=$B77),
    --(INDIRECT(Detail_Whse_Class)<>"O"),
    --Left((INDIRECT(Detail_Whse_Hospital),3)="031"),
    --INDIRECT(Detail_Proj_Days))
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am not positive I have interpreted your suggestion correctly - since it appears the parentheses in your formula do not match up exactly. But once I correct for that I am still getting a 0 (ZERO) for my cell results. Great idea though - and again I may not be interpreting this precisely (see below)

    =SUMPRODUCT(--(INDIRECT(Detail_Whse_Region)=$B77),
    --(INDIRECT(Detail_Whse_Class)<>"O"),
    --Left(INDIRECT(Detail_Whse_Hospital),3)="031",
    --INDIRECT(Detail_Proj_Days))

  4. #4
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I take it back - it DID work. One of the other criteria was not satisfied on the row I was testing, but once I copied it down the values showed up. Thanks - this is a great help!

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by wittigbg View Post
    it appears the parentheses in your formula do not match up exactly.
    Sorry about that but glad it worked after you corrected my boo-boo.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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