+ Reply to Thread
Results 1 to 3 of 3

Sumproduct with multiple text criteria

  1. #1
    Robert
    Guest

    Sumproduct with multiple text criteria

    I have seen this question answered a couple times but none fit my situation.
    I am trying to use the following formula:
    =SUMPRODUCT(--(AL2:AL50000="*GGF*"),--(AD2:AD50000="Charleston")) to search
    two columns of text and return the count for the rows that meet both
    criteria. (If AL2 has the text GGF somewhere in the cells text string and AD2
    has the text Charleston in its cell, then it would count 1.)
    It is not giving me the count that is correct, it is returning a zero. My
    problem is that the text string in column AL contains different strings of
    text, and I only want to count the ones that contain the letters "GGF". The
    text may appear as part of a longer string, not just by itself in the cell,
    and I do want to count it in those cases. It appears the SUMPRODUCT formula
    does not let me use the "*GGF*" method of searching a string for specific
    text, like the COUNTIF statement does.
    Any thoughts on how to get around this problem?
    Thanks
    --
    Robert

  2. #2
    Roger Govier
    Guest

    Re: Sumproduct with multiple text criteria

    Hi Robert

    Try
    =SUMPRODUCT(--(ISNUMBER(FIND("GGF",AL2:AL50000))),--(AD2:AD50000="Charleston"))

    Regards

    Roger Govier


    Robert wrote:
    > I have seen this question answered a couple times but none fit my situation.
    > I am trying to use the following formula:
    > =SUMPRODUCT(--(AL2:AL50000="*GGF*"),--(AD2:AD50000="Charleston")) to search
    > two columns of text and return the count for the rows that meet both
    > criteria. (If AL2 has the text GGF somewhere in the cells text string and AD2
    > has the text Charleston in its cell, then it would count 1.)
    > It is not giving me the count that is correct, it is returning a zero. My
    > problem is that the text string in column AL contains different strings of
    > text, and I only want to count the ones that contain the letters "GGF". The
    > text may appear as part of a longer string, not just by itself in the cell,
    > and I do want to count it in those cases. It appears the SUMPRODUCT formula
    > does not let me use the "*GGF*" method of searching a string for specific
    > text, like the COUNTIF statement does.
    > Any thoughts on how to get around this problem?
    > Thanks


  3. #3
    Robert
    Guest

    Re: Sumproduct with multiple text criteria

    Exactly what I needed - thanks a million!
    --
    Robert


    "Roger Govier" wrote:

    > Hi Robert
    >
    > Try
    > =SUMPRODUCT(--(ISNUMBER(FIND("GGF",AL2:AL50000))),--(AD2:AD50000="Charleston"))
    >
    > Regards
    >
    > Roger Govier
    >
    >
    > Robert wrote:
    > > I have seen this question answered a couple times but none fit my situation.
    > > I am trying to use the following formula:
    > > =SUMPRODUCT(--(AL2:AL50000="*GGF*"),--(AD2:AD50000="Charleston")) to search
    > > two columns of text and return the count for the rows that meet both
    > > criteria. (If AL2 has the text GGF somewhere in the cells text string and AD2
    > > has the text Charleston in its cell, then it would count 1.)
    > > It is not giving me the count that is correct, it is returning a zero. My
    > > problem is that the text string in column AL contains different strings of
    > > text, and I only want to count the ones that contain the letters "GGF". The
    > > text may appear as part of a longer string, not just by itself in the cell,
    > > and I do want to count it in those cases. It appears the SUMPRODUCT formula
    > > does not let me use the "*GGF*" method of searching a string for specific
    > > text, like the COUNTIF statement does.
    > > Any thoughts on how to get around this problem?
    > > Thanks

    >


+ 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