In 2-dimensional array, find all instances of a value, and sum corresponding values in another column (column locations may vary)

diversification

New Member
Joined
Jun 24, 2020
Messages
37
Office Version
  1. 365
Platform
  1. Windows
Hello, I'm not very good at describing what I'm trying to do, so I'm going to lead with screens of my Sheets, and then describe what's I'm trying to do from there. I apologize if this is a roundabout way to ask my question.

Sheet1:
Sheet1.JPG


Sheet2:
Sheet2.JPG


The Function I'm trying to write will be to tally the "Count" in Sheet1, Column B. The Function will look at Sheet1, Column A to get the lookup value (a fruit name) and then look for all matches in Sheet2, and for any matches it finds, it'll sum up the corresponding values in the "Number" column (located at Sheet2 Column D in this example.)

If I know where the Fruit and Number columns will be located in Sheet2 ahead of time, I can write a very simple SUMIF Function that I can fill down (if fact I did for this example.) ---> =SUMIF(Sheet2!B:B,Sheet1!A2,Sheet2!D:D)

However, I unfortunately do not know the locations of the columns ahead of time since the data source sometimes will vary, and in practice, changing the formula to fit the data, or re-arranging the data is far from ideal. So for example, sometimes on Sheet2, the "Fruit" entries may be in Column C, while the "Number" entries may be in Column F (or vice versa, etc, etc.)

I was thinking perhaps an index / match, but I can't get that to work. Any help would be very much appreciated.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Hi & welcome to MrExcel.
How about
=SUMIFS(INDEX(Sheet2!A:L,,MATCH("Number",Sheet2!A1:L1,0)),INDEX(Sheet2!A:L,,MATCH(A1,Sheet2!A1:L1,0)),A2)
 
Upvote 0
Haven't tried it, but I'm guessing that'll work just from looking at the differences in syntax from what I tried. I think my problem was that I have misunderstood the proper way to use the MATCH function. I was having it look through the entirety of the range I was Indexing (A:L) instead of limiting it to one row (A1:L1.) I'll try this solution and let you know.
 
Upvote 0
Ok, so this works if I know what row the headings will be in, so thank you! To make this formula even more generic though, is there a way that I could have it first identify which row the headers are in, and then use that to plug into the formula? Is that a lot more complex?
 
Upvote 0
You would need to use structured tables for that.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top