diversification
New Member
- Joined
- Jun 24, 2020
- Messages
- 37
- Office Version
- 365
- Platform
- 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:
Sheet2:
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.
Sheet1:
Sheet2:
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.