+ Reply to Thread
Results 1 to 4 of 4

Sum 3 category values from a item cross referenced from a table, SUM, SMALL, INDEX, MATCH?

  1. #1
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Sum 3 category values from a item cross referenced from a table, SUM, SMALL, INDEX, MATCH?

    A little hard to explain, so look at the attached workbook.

    The reference table is in $B$5:$C$15 which list the item in B and each corresponds to a category in C. In $E$5:$E$15 I have a list of the items and each corresponds to a value in the adjacent column (F). In $H$5:$H$7, I list the 3 categories. In I5:I7 I need a formula to look up the first 3 items (if there are 4 items, the 4th should be ignored), lookup the item in the reference table and get the category, and then sum those item. So for Fruit in I5, the answer should be 9 (1 for Pear + 3 for Banana + 5 for pineapple... the formula ignores Peach in E13); I6 should be 18, and I7 should be 23.

    I have formulas where I've tried different strategies in each of the I cells, feel free to delete them...
    Last edited by HeyInKy; 11-05-2015 at 01:45 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Sum 3 category values from a item cross referenced from a table, SUM, SMALL, INDEX, MA

    Edit; nvm. didn't completely read the problem :|
    Last edited by Speshul; 10-26-2015 at 04:15 PM.
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  3. #3
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Re: Sum 3 category values from a item cross referenced from a table, SUM, SMALL, INDEX, MA

    Ok, to explain another way, it should look at column E, cross reference the item in E with the table in B & C, then return the sum value of only the first 3 items in that category. So it finds Pear (Fruit = 1), then Banana (Fruit = 3), and finally Pineapple (Fruit = 5), ignores the 4th fruit, Peach, and returns a sum of 9 in I7.
    Last edited by HeyInKy; 10-26-2015 at 04:28 PM.

  4. #4
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Re: Sum 3 category values from a item cross referenced from a table, SUM, SMALL, INDEX, MA

    Bump. Any help today is greatly appreciated!

+ 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. Replies: 10
    Last Post: 05-22-2014, 06:44 AM
  2. problems using SMALL and INDEX(MATCH) in a table
    By lookingforhelp2014 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-16-2014, 11:12 AM
  3. [SOLVED] INDEX MATCH and MIN/SMALL to pull first and second smallest values from array
    By tlafferty in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-18-2014, 04:03 AM
  4. Duplicate values returned using Index Match Lookup with Small()
    By jacob@thepenpoint in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-18-2013, 10:48 AM
  5. [SOLVED] Input the cross referenced value after looking up a table?
    By Fallout in forum Excel General
    Replies: 2
    Last Post: 07-20-2012, 09:56 PM
  6. Index and match function for same item in the table
    By ronlau123 in forum Excel General
    Replies: 3
    Last Post: 05-15-2011, 02:11 AM
  7. Need data cross-referenced in multiple tables: VLOOKUP vs. Match vs. Index
    By James McMurray in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-05-2008, 02:53 PM

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