How to combine index, match and sumif function here?

December 5, 2017 at 12:25:08
Specs: Windows 7
I need the sum of items we have in sheet 2, as we have an apple, lemon, and grapes repeated multiple times in sheet 2 but I want to place the value in Expense column in sheet 1 combining the values in front of the fruite name. Example: In front of apple it should give me the sum which is Apple = Expense = $11.8 because the apple has $2, $3, $5 and $1.8 values in Sheet 2.

Sheet 1 Expense

Apple $80 ($???)
Lemon $30 ($???)
Grapes $25 ($???)

Sheet 2 (Expense summary)

Apple $2
Apple $3
Apple $5
Lemon $1.5
Grapes $2.9
Apple $1.8
Lemon $4
Grapes $1


See More: How to combine index, match and sumif function here?

Reply ↓  Report •

#1
December 5, 2017 at 13:37:04

Reply ↓  Report •

#2
December 5, 2017 at 18:16:07
You didn't tell us what cells your data is in, so I'll assume that it starts in A1 of both sheets and that your formula is on Sheet1.

=SUMIF(Sheet2!A1:A8,A2,Sheet2!B1:B8)

I don't know why you think you need INDEX and MATCH. This a basic SUMIF situation.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#3
December 6, 2017 at 06:40:57
Thank you but its not that simple as you think, I guess you didn't get the point.

Its to match with another data set to get the value on sheet 1. I don't want to apply vlookup because of multiple values in sheet 2. When I apply index and match its pulling the first value from the multiple values of the data in sheet 2. Now think in that way that on sheet 1 I have 1 value for apple which is $80 and on sheet 2 i have multiple values of apple which is $2, $3, $5 and $1.8 and I want the answer on sheet 1 next to column $80 value which sums up the values on sheet 2 when it match the criteria apple. Hope you get the question now. :)


Reply ↓  Report •

Related Solutions

#4
December 6, 2017 at 06:41:53
yes its the same question with example.

Reply ↓  Report •

#5
December 6, 2017 at 11:22:35
I have deleted the previous thread so as not to confuse matters.

Before posting any more example data in this forum, please click on the How-To link at the end of this post and read the instructions on how to format example data so that it is easier for us to read. Don't forget to use Column letters and Row numbers as shown in the example.

That said...

I started with this layout, based on your example data:

Sheet1

        A          B         C
1     Item
2     Apple       $80      
3     Lemon       $30      
4     Grapes      $25      

Sheet2

      A            B
1   Apple         $2
2   Apple         $3
3   Apple         $5 
4   Lemon         $1.5
5   Grapes        $2.9
6   Apple         $1.8
7   Lemon         $4
8   Grapes        $1

I then put the formula I posted earlier in C2:

Sheet1

        A          B         C
1     Item
2     Apple       $80      =SUMIF(Sheet2!A1:A8,A2,Sheet2!B1:B8)
3     Lemon       $30      
4     Grapes      $25      

I now have this:

        A          B         C
1     Item
2     Apple       $80      11.8
3     Lemon       $30      
4     Grapes      $25      

Since I managed to come up with the answer that you wanted (11.8) and placed it "on sheet 1 next to column $80 value" using the example data that you posted, I guess you'll have to tell me what "point" I am not getting.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question