+ Reply to Thread
Results 1 to 3 of 3

Counting cells for multiple wildcard text

  1. #1
    Forum Contributor
    Join Date
    06-10-2005
    MS-Off Ver
    2007
    Posts
    223

    Counting cells for multiple wildcard text

    Hi,
    I have a list of mutual fund names in column A. I have a sumproduct formula that sums up the market value all the Morgan Stanley funds for particular clients. But I want to make sure that if Morgan Stanley incorporates the word "Fidelity" (or some other fund name) into the title of their fund, that I don't double count the values. So I'm trying to get a formula that will test for any instance of "Morgan Stanley" and "Fidelity" that is contained within the same cell; but for the entire list of mutal funds.
    Any help would be appreciated.
    Thanks,
    Phillycheese

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Counting cells for multiple wildcard text

    Do a SUMPRODUCT using the Fidelity criteria and subtract this from the other formula. If there are no Fidelity funds then the SUMPRODUCT formula will return zero and nothing will be subtracted from the first formula, otherwise it will factor out the fidelity funds from the first SUMPRODUCT.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  3. #3
    Forum Contributor
    Join Date
    06-10-2005
    MS-Off Ver
    2007
    Posts
    223

    Re: Counting cells for multiple wildcard text

    Palmetto,
    If there were a fund called "Morgan Stanley New Fidelity Fund", I agree that subtracting it out from the Fidelity total is a good way to account for this.
    So if column A has all my funds, B has the customer, and C has the total dollars, I'm okay with the formula:
    =SUMPRODUCT(--(ISNUMBER(FIND("FIDELITY",INDIRECT("'"&J$9&"'!$A$2:$A$50000")))),--(INDIRECT("'"&J$9&"'!$B$2:$B$50000")="Mike"),--(INDIRECT("'"&J$9&"'!$C$2:$C$50000")))
    Note: the INDIRECT is there to make my life easier by pulling in data to a summary page from the particular sheet name that will change every month.

    My problem is that I'm not sure how to write the formula to identify the case where a fund name contains the words "morgan stanley" and "fidelity". Sorry if I didn't explain it so well in my post.

    Thanks,
    Phillycheese

+ 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