+ Reply to Thread
Results 1 to 12 of 12

INDEX Partial MATCH and Total

  1. #1
    Registered User
    Join Date
    10-02-2014
    Location
    Scotland
    MS-Off Ver
    2007
    Posts
    68

    INDEX Partial MATCH and Total

    Afternoon guys

    I am trying to partial match using INDEX MATCH and think I am almost there

    In the example (see attachment) I want to find all the "Food Shop" in November so I was trying to partial match using "*nov*" and then I need all the matches added together

    Can anyone see where I have gone wrong?

    Thanks!

    Jonny
    Attached Files Attached Files

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: INDEX Partial MATCH and Total

    Try

    =SUMPRODUCT(--(D1:D11=I11),--(TEXT(B1:B11,"mmm")=I10),F1:F11)

    Change I11 to just Nov (no *'s)


    Will you also need to account for the year?

  3. #3
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: INDEX Partial MATCH and Total

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Registered User
    Join Date
    10-02-2014
    Location
    Scotland
    MS-Off Ver
    2007
    Posts
    68

    Re: INDEX Partial MATCH and Total

    Do you mean like a year to date? That would be amazing if possible!

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: INDEX Partial MATCH and Total

    No, what I meant was in column B, if you have dates from multiple years.
    Say you have 11/15/2014 and 11/15/2013
    Should they both be counted, or only the 2014 ?

  6. #6
    Registered User
    Join Date
    10-02-2014
    Location
    Scotland
    MS-Off Ver
    2007
    Posts
    68

    Re: INDEX Partial MATCH and Total

    Ohh sorry yeah they should definitely be counted

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: INDEX Partial MATCH and Total

    Quote Originally Posted by flapface View Post
    they should definitely be counted
    I assume you mean they should both becounted...
    No adjustments required then..

    Also, just a heads up.
    Make sure the date range you're using doesn't contain blanks.
    They would be counated as January.

    This shouldn't be a problem, since you have a 2nd criteria "Food Shop"
    Presumably if the date is blank, then the column D would be blank as well..

  8. #8
    Registered User
    Join Date
    10-02-2014
    Location
    Scotland
    MS-Off Ver
    2007
    Posts
    68

    Re: INDEX Partial MATCH and Total

    Morning guys

    The sumproduct formula really slows down my spreadsheet is there another way round this? I have seen similar problems use INDEX MATCH however I am not that clued up on the function I must be close with this formula

    =INDEX(F:F,MATCH("Nov",IF(C:C=I11,B:B),0))??

    Thanks guys!

    Jonny

  9. #9
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: INDEX Partial MATCH and Total

    The sumproduct formula really slows down my spreadsheet is there another way round this?
    Don't use whole columns reference in SUMPRODUCT.

    Alternative you can use a helper(and hidden if you like) column with this formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and then a SUMIF to get your result.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  10. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: INDEX Partial MATCH and Total

    Quote Originally Posted by flapface View Post
    The sumproduct formula really slows down my spreadsheet
    This is good advice.
    Quote Originally Posted by Fotis1991 View Post
    Don't use whole columns reference in SUMPRODUCT.
    +1

  11. #11
    Registered User
    Join Date
    10-02-2014
    Location
    Scotland
    MS-Off Ver
    2007
    Posts
    68

    Re: INDEX Partial MATCH and Total

    That is soooo much better! I used the full columns as this is a data sheet that will be entered for many years I set it to the first 1000 entries is there a way the formula can take only the rows of the table perhaps a dynamic range?

  12. #12
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: INDEX Partial MATCH and Total

    I believe is better to use whole columns reference in SUMIF, rather than to create dynamic ranges. There is no reason for that.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. finding partial text in an index match formula
    By garyaw in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 09-30-2015, 04:27 PM
  2. Replies: 2
    Last Post: 09-27-2014, 04:34 PM
  3. [SOLVED] how to get the total from combination of index and match function
    By alimamak in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-22-2012, 11:33 PM
  4. Index Match nesting w/ partial text string criteria
    By dohearn in forum Excel General
    Replies: 1
    Last Post: 10-25-2011, 03:42 PM
  5. Index and Match-REP Total
    By bscarano@gmail.com in forum Excel General
    Replies: 4
    Last Post: 02-18-2005, 10:06 AM

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