Forum Discussion
Sum of values if identifier matches
Good day, everyone!
I have two tables that provide information to each other. Please see the example below before I proceed explaining.
TABLE 1
| Item Code | Total |
| Code A | 2000 (1000 + 1000) |
| Code B | 7000 (2000 + 5000) |
| Code C | 6000 (1000 + 6000) |
TABLE 2
| Demand | Units |
| Code A, Code C | 1000 |
| Code B | 2000 |
| Code A | 1000 |
| Code B, Code C | 5000 |
In table 1, I enter the Codes separately in each row. In table 2, some of the item codes from Table 1 are combined and also manually entered by me; I always separate them with a comma and a single space (never any different because I use VBA for it).
In table 2, the Units are a value that I have to look up in a different system. For purposes of my spreadsheet (and this example), I enter it manually.
The trick is the second column in table 1 (Total). The text highlighted in RED is to demonstrate what values should be added to give the correct total.
My question is, without using VBA (because I was asked to limit VBA usage), what Functions can I use so that column looks up the Item Codes in table 1 within the Demand column in table 2 and adds the appropriate values found in the Units column in table 2?
Any help is greatly appreciated.
6 Replies
I can't speak for others, but speaking for myself, I fail to see the logic that would, based on some kind of lookup of the entries in the first column of Table 2, yield the results in the second.
It seems to me that you may be making assumptions about how this is working, assuming that those assumptions are obvious, but if you really expect to have a clear answer--i.e., a clear formula that is based on functions--then the whole situation needs to be explained more fully.
Does the second column of table one, for one thing, actually contain those numbers shown in red, or are you showing us the obvious math (except for Code C, where it dosn't work). And, given Code C's confusing components, what is the consistent relationship between the number in black and the two in red?
Your example, in short, is hard to reverse engineer. So help us help you by explaining things more clearly.
- The content in red is the obvious math. It doesn't actually show up in the cell at all. That's what I was trying to explain in the 2nd to last paragraph. The number in black is the actual result that shows up in the cell; it is the sum of the two values in red (in table 1).
For example. Code C shows up twice in table 2: once with a value of 1000 and once with a value of 5000. Therefore, 5000 + 1000 = 6000. The 6000 is the actual value that I need to be shown in the second column of the first table.
Anything else I should clear up?
