|While there may be other methods to accomplish this goal, the method I am about to describe will introduce the concept of "Helper Columns". |
A Helper Column is a column that can be used to store intermediate results that can help simplify further formulas. "Helper Column" is not an Excel term, so you won't find it in the Excel Help files. It is merely a term that has become common among Excel users. A Google search will bring up many different uses for Helper Columns.
Helper Columns can be placed anywhere they make sense and can even be hidden so that they don't impact the visual layout of the spreadsheet. In this case, I am going to include them right next to the 2 data sets, just to show you the concept.
In the table below, we don't have unique values that we can use the SUMIF function with. e.g. we have 1000 alpha and 1000 beta, so we can't SUMIF on 1000 by itself. However, we can SUMIF on 1000alpha and 1000beta. Note the elimination of the space.
Let's say we are starting with this:
A B ..... F G
1 1000 alpha 1000 beta
2 1010 alpha 1010 alpha
3 1000 beta 1000 alpha
4 1010 beta 1030 omega
I am going to insert a Helper Column at the beginning of each data set and use the Concatenation operator to combine the 2 values that we need to SUMIF.
A B C ..... G H I
1 =B1&C1 1000 alpha =H1&I1 1000 beta
2 =B2&C2 1010 alpha =H2&I2 1010 alpha
3 =B3&C3 1000 beta =H3&I3 1000 alpha
4 =B4&C4 1010 beta =H4&I4 1030 omega
Now I have a table that looks like this:
A B C ..... G H I
1 1000alpha 1000 alpha 1000beta 1000 beta
2 1010alpha 1010 alpha 1010alpha 1010 alpha
3 1000beta 1000 beta 1000alpha 1000 alpha
4 1010beta 1010 beta 1030omega 1030 omega
For the merged table, you will also create a Helper Column but it will contain the concatenated values - which are unique - and use the SUMIF function by referencing the Helper Columns (A and G) instead of the actual data columns (A:B and H:I).
As noted earlier, the Helper Columns can all be hidden (including the one used for the merged data set) so that the user only sees "clean data" even though what is seen isn't what is actually being used by the formulas.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
message edited by DerbyDad03