Index & Match: Return Sum of Indexed Values if Match Repeats

Hi Guys:

I hope you're all doing well. I'm looking for an index/match function which will add the index values when match finds repeated values. I've changed the names of the tenants I'm working with for this example. When there's a new lease, a new row is created in sheet 1. There was a new lease for Blockbuster in Q4 and I need the function to return 11,000 (below in bold) in sheet two, i.e., the sum of the old lease payments 2,000 and the new lease payments 9,000. Oh, and if you have to return some videotapes at Blockbuster, remember to be kind and please rewind!

SHEET 1

A	B	C	D	E	F
TENANT	09/19	10/19	11/19	12/19	TOTAL
Sears	3,000	3,000	3,000	3,000	12,000
Blockbuster	2,000	0	0	0	2,000
Blockbuster	0	3,000	3,000	3,000	9,000
Toys R Us	4,000	4,000	4,000	4,000	16,000

SHEET 2

A	B
TENANT	TOTAL
Sears	12,000
Blockbuster	11,000
Toys R Us	16,000

Not sure you need an INDEX MATCH - SUMIF should suffice based on what you've shown us.

Try: sumif(A:A,A3,F:F)

Ali beat me to the simple answer. But you could use a Pivot Table for more interesting analyses.

You can simply use SUMIF instead of Lookup operation.

Formula:

Please Login or Register  to view this content.

Edit: Too slow!

Use the formula in post #5 - enter it then drag copy it down. Just try it!

We know it does! Glad to have helped.

You're welcome. Thanks for the rep.