Results 1 to 3 of 3

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

This is a discussion on Sum if formula using "left" or "find" for range within the Excel Questions forums, part of the Question Forums category; Not sure how to sum column I when the first three letters in column B are "ISP" using the following: ...

  1. #1
    Board Regular
    Join Date
    Mar 2008
    Posts
    162

    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
    16,669

    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 2007/2010

    Be responsible for the questions you post. If you don't reply to answered questions, be it just a simple "yes, that worked," or even "thanks," the time someone took to help you goes unrewarded.

  3. #3
    Board Regular
    Join Date
    Mar 2008
    Posts
    162

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

    Thanks HotPepper,

    got it to work with my application...

    Ben

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