Using INDEX and MATCH to find SUM

[JakeSanchard]

Hi all. Hope you're doing well.

I'm looking to find out how to use INDEX and MATCH in a way that will sum all the values in Column 2 IF the value in column 1 is equal to the value in cell R2C5. Column 1 will contain duplicate values, but it's corresponding value in Column 2 may vary. e.g. Column 1 contains the name of a county, column 2 contains the count of a specific building in that county. My formula wants to be able to figure out how many overall buildings there are.

With so many types of building (warehouse, store, residence, utility etc.) the whole array is very large and wanted a quick way to get the sum, if this is possible.

(numbers have been generated using RANDBTEWEEN 2,999 ;-))

Thank you.

[JohnTopley]

Try

=SUMIF(RC[-5]:R[34]C[-5],RC[-1],RC[-4]:R[34]C[-4])

Or more sensible in my view

=SUMIF(A2:A36,E2,B2:B36)

[6StringJazzer]

We discourage posing a question in a way that assumes what the answer is going to be. It's better to describe what result you want, like "Get counts of all buildings by county". In this case INDEX and MATCH are rabbit holes.

Put this formula in F2 and copy down.

Please Login or Register  to view this content.

[JakeSanchard]

Thank you both so much. You're certainly right about the rabbit hole, I tried to overcomplicate it and your formulas are very elegant.

[6StringJazzer]

Thank you for the rep!

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."