Sum if formula using "left" or "find" for range

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Sum if formula using "left" or "find" for range

  1. #1
    Board Regular
    Join Date
    Mar 2008
    Posts
    172
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sum if formula using "left" or "find" for range

     
    Not sure how to sum column I when the first three letters in column B are "ISP" using the following:

    =SUM(IF('QUOTE STATUS'!$M$3:$M$5000>=AP1,IF('QUOTE STATUS'!$M$3:$M$5000,IF(FIND("ISP",'QUOTE STATUS'!$B3:$B$5000),IF('QUOTE STATUS'!$G$3:$G$5000="W",'QUOTE STATUS'!$I$3:$I$5000,"")))))

    AP1 & AP2 are a date range in column M
    "W" = order won in column G

    Thanks for your help in advance...!

    Ben

  2. #2
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,225
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum if formula using "left" or "find" for range

    =sumproduct(--('quote status'!$m$3:$m$5000>=ap1),--(left('quote status'!$b$3:$b$5000,3)="isp"),--('quote status'!$g$3:$g$5000="w"),'quote status'!$i$3:$i$5000)
    Office 2010/365

  3. #3
    Board Regular
    Join Date
    Mar 2008
    Posts
    172
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum if formula using "left" or "find" for range

      
    Thanks HotPepper,

    got it to work with my application...

    Ben

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com