+ Reply to Thread
Results 1 to 2 of 2

Array Formulas and Text String LookUps

  1. #1
    Registered User
    Join Date
    04-09-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    2

    Array Formulas and Text String LookUps

    Please can someone help me with the attached?

    Essentially I have one sheet of data and one sheet to summarise that data. The bits that I am summarising have varying complexities of formulas, from simple SUMIFs to SUMIF array formulas.

    The real complexities come in when I start referring to text within the array - which by the way is essential to it all.

    I have simplified my task at hand right down to the attached spreadsheet, but what ever the solution is it needs to be scalable.

    A big smiley face to anyone who can solve the red cell using either an array formula or VLookup (I presume).

    My thanks in advance!

    Mike
    Attached Files Attached Files
    Last edited by a8vry; 04-09-2009 at 12:00 PM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Array Formulas and Text String LookUps

    One way

    F11: =SUM(IF(RIGHT(Data!A2:A5,LEN(A11)-1)=REPLACE($A11,1,1,""),IF(Data!D2:D5="f",Data!E2:E5,0)))
    entered with CTRL + SHIFT + ENTER

    You could use a Sumproduct (non CSE)

    F11: =SUMPRODUCT(--(RIGHT(Data!A2:A5,LEN(A11)-1)=REPLACE(A11,1,1,"")),--(Data!D2:D5="f"),Data!E2:E5)

    Slightly quicker but not by much...
    Last edited by DonkeyOte; 04-09-2009 at 12:12 PM.

+ 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