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

✔ Best Answer

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 linkat 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:

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

Sheet2A 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 $1I then put the formula I posted earlier in C2:

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

A B C 1 Item 2 Apple $80 11.8 3 Lemon $30 4 Grapes $25Since 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

Is this question related to your earlier post?

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

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

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. :)

yes its the same question with example.

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 linkat 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:

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

Sheet2A 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 $1I then put the formula I posted earlier in C2:

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

A B C 1 Item 2 Apple $80 11.8 3 Lemon $30 4 Grapes $25Since 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

Ask Your Question

Weekly Poll

Do you think Microsoft Office is too confusing to use?

Discuss in The Lounge

Poll History