+ Reply to Thread
Results 1 to 3 of 3

ARRAY SEARCH & TALLY: looking for a way to add up a list of array items that CONTAIN, not

  1. #1
    Registered User
    Join Date
    03-02-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    2

    ARRAY SEARCH & TALLY: looking for a way to add up a list of array items that CONTAIN, not

    Hi,
    I am looking for a way to add up a list of array items that CONTAIN, not MATCH, a search query...

    eg: I know how to search an array to find a match that holds '/home/' but this only matches an exact cell match.
    "{=SUM((A21:A34=A1)*(B21:B34))}"

    how would I amend the '(A21:A34=A1)' part so that the condition can expand to contain the target text '/home/' as ANY PART OF the search inquiry, not just return the exact matches:
    in the example below:
    "{=SUM((A21:A34=A1)*(B21:B34))}" = "3000" - ie 3x exact matches
    "{=SUM((A21:A34='anything that contains '/home/')*(B21:B34))}" = 11 matches, ie total 3352


    SAMPLE DATA
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ROw 1-2
    /home/ 3000 {=SUM((A21:A34=A1)*(B21:B34))}
    home home =MID(A21,(FIND(A3,A21,1)),LEN(A3))
    ...

    ROW 21+
    COLUMN A COLUMN B
    SAMPLE DATA pageviews
    /home/ 1000
    /home/player/ 55
    /home/player/teamid 33
    /home/ 1000
    /home/player/ 55
    /home/player/teamid 33
    /home/player/ 55
    /home/player/teamid 33
    /home/ 1000
    /home/player/ 55
    /home/player/teamid 33
    destination 777
    team 333
    team/england 3333
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    THANKS for any help you can provide

    DAVE

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: ARRAY SEARCH & TALLY: looking for a way to add up a list of array items that CONTAIN,

    Dave

    You can use wildcards with both SUMIF and COUNTIF.

    For example if the data wa in A1:A14,
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    which results in 3352.

    Also, if there's multiple criteria you have SUMIFS and COUNTIFS which also allow wildcards.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    03-02-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: ARRAY SEARCH & TALLY: looking for a way to add up a list of array items that CONTAIN,

    thanks! have replaced hard-string with "*"&A5&"*" to get 100% fit but is a great answer!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

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