+ Reply to Thread
Results 1 to 3 of 3

How to match partial cell text and sum relating values for multiple matches

  1. #1
    Registered User
    Join Date
    10-30-2007
    Posts
    2

    How to match partial cell text and sum relating values for multiple matches

    Hello, I have two worksheets I have to match and sum data from. In the first work sheet I have a phrase I need to lookup in the second sheet from a list of website addresses, but there will be multiple matches in this address list and I need to sum the corresponding values. For example: sheet one cell A1 is ATM_12 and in sheet two E2:E6576 have web addresses like http://abc.com/index.php?source=google&landing=atm_12 and corresponding values in N2:N6576

    I have figured out how to sum on a exact text search using: =SUMIF(Sheet3!E2:E6576,"http://www.abc.com/index.php?source=google&landing=ATM_12",Sheet3!N2:N6576) but since there is a list of about 50 phrases and the web address domain varies, I cannot effectively use an exact search like this.

    Anyone have any suggestions?

    Thanks in advance-

    Ryan

  2. #2
    Forum Contributor Lotus123's Avatar
    Join Date
    11-07-2005
    Location
    Texas
    MS-Off Ver
    2007
    Posts
    153
    Ryan:

    Give this a shot:

    Please Login or Register  to view this content.
    The formula is an array and must be confirmed with CTRL-SHIFT-ENTER instead of simply ENTER (hold the CTRL and SHIFT key while pressing ENTER).
    Last edited by Lotus123; 10-30-2007 at 12:08 PM.

  3. #3
    Registered User
    Join Date
    10-30-2007
    Posts
    51
    I think you might find a solution combining the SUMPRODUCT, ISNUMBER and SEARCH functions.

    Something like:

    =SUMPRODUCT((ISNUMBER(SEARCH("ATM_12",Sheet3!E2:E6576))*(Sheet3!N2:N6576))
    Last edited by MickeyGreen; 10-30-2007 at 12:14 PM.

+ 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