SUM Cells based on Index Match

Question

I've used Excel for a long time, but I've just started playing around with macros, Index, VLOOKUP and other features. They're wonderful, but I've come to a roadblock.

I've managed to get Excel to paste the information below to another worksheet on the next blank row every time I press a button in sheet 1 sheet.

What I want to do now is SUM all the sale amounts and the discount amounts when I lookup an employee name.

The formulas I have on another sheet to look up the discount and sale amounts are:

=INDEX(Sales!$E$1:$E$1000,MATCH($A$1,Sales!$A$1:$A$1000,0)+14,0) =INDEX(Sales!$E$1:$E$1000,MATCH($A$1,Sales!$A$1:$A$1000,0)+16,0)

However, it seems as though it's just finding the first match.

Further down the sales sheet, there is another sale by this employee and I want the result to sum all the discount given and all the total sales amount.

I assume this is possible, but my Googling has found nothing.

Any help would be greatly appreciated!

a few questions: What does the data table look like? Why can't you just filter on employee name or create a pivot table to show Sales & Discounts by Employee? Also, it sounds as if SUMIF might be the function you are seeking. Something like: B3 = SUMIF('dataTableRange[SalesPerson]',A1,'sumRange[Price]') — Sam
Without looking at all of the details, I noticed you said this: "However, it seems as though it's just finding the first match." That is how MATCH works - it only does find the first match. If you want to find all of the matches, you will have to use another method. Based on your description, it sounds like you may need SUMIF or SUMIFS. — ImaginaryHuman072889
I've had a play around with the suggested formulas but it's not quite doing what I want. Using this code =SUMIF(Sales!A4:A5,A1,Sales!E21) Returns the value of the first sale. However, if I expand the range from A4:A5 to A1:A3000, it returns a zero. Not sure why it's doing that. What I need is for the formula to find all instances of the salesperson's name and SUM all values that are offset by 4,16 from those cells. Is that possible? — TCJ
Also, Sam, the information gets pasted into the Sales sheet in the same format as the Sale picture in my post, one below the other. This is what I want so I can see each transaction. I then need to be able to get the data of how much each salesperson has sold at the end of the day/week. I could go through and manually copy and paste each sale amount into a new table for each salesperson, but I'm looking for an automatic way to do it to save time. — TCJ

W_O_L_F W_O_L_F · Accepted Answer · 2018-10-18T09:52:42

Maybe this could be a solution. Where "a" would be "billy" in your case and "b" would be equal to "Subtotal".

=SUMIF(A1:A8,"a",INDEX(A1:C8,MATCH("a",A:A,0)+1,3))

I believe the"+1" would be +13 in your example. (the difference in rows between billy and subtotal.) And 3 would be 5 (col E)

SUM Cells based on Index Match

2 Answers