Using table row & column heading for data extract summary

[eduardo1966]

Here is a problem that is driving me nuts. I have two tables. The first is generated from a trial balance with department-level detail. In this report I have department numbers going across and account numbers going down the page. The second table is a list of account and department combinations. I need to use this list to "cherry pick" the values in the trial balance report.

The following is a simplified example of the data I am working with:


Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="FONT-WEIGHT: bold">Department</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="FONT-WEIGHT: bold">Account</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">100</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">200</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">300</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">400</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">500</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">1947</TD><TD style="TEXT-ALIGN: right">443.77 </TD><TD style="TEXT-ALIGN: right">969.51 </TD><TD style="TEXT-ALIGN: right">652.91 </TD><TD style="TEXT-ALIGN: right">914.01 </TD><TD style="TEXT-ALIGN: right">448.99 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2240</TD><TD style="TEXT-ALIGN: right">645.41 </TD><TD style="TEXT-ALIGN: right">413.39 </TD><TD style="TEXT-ALIGN: right">686.03 </TD><TD style="TEXT-ALIGN: right">341.49 </TD><TD style="TEXT-ALIGN: right">799.64 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2242</TD><TD style="TEXT-ALIGN: right">708.18 </TD><TD style="TEXT-ALIGN: right">95.08 </TD><TD style="TEXT-ALIGN: right">225.11 </TD><TD style="TEXT-ALIGN: right">272.31 </TD><TD style="TEXT-ALIGN: right">554.33 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2639</TD><TD style="TEXT-ALIGN: right">849.45 </TD><TD style="TEXT-ALIGN: right">52.08 </TD><TD style="TEXT-ALIGN: right">759.91 </TD><TD style="TEXT-ALIGN: right">739.21 </TD><TD style="TEXT-ALIGN: right">812.24 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">6400</TD><TD style="TEXT-ALIGN: right">698.19 </TD><TD style="TEXT-ALIGN: right">86.64 </TD><TD style="TEXT-ALIGN: right">412.01 </TD><TD style="TEXT-ALIGN: right">90.86 </TD><TD style="TEXT-ALIGN: right">505.13 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>Data Needed for</TD><TD style="TEXT-ALIGN: right"></TD><TD style="FONT-WEIGHT: bold">Account</TD><TD style="FONT-WEIGHT: bold">Department</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">1947</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">200</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2240</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">100</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2240</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">200</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2240</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">300</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2639</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">500</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">6400</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">300</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD>Function would return</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">3,938.59 </TD><TD>sum of cells C3, B4, C4, D4, F6, & D7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Sheet1​

I can get an answer the old-fashioned way with index and match formulas running down the second list, but I've got to believe there is a tighter alternative. Ideally, I would like the formula to be elegant and in just one cell. I thought using "countif" would do the trick, but there is a limitation in that function that I just can't get past. Any ideas out there?

 

[eduardo1966]

Sorry, I forgot to add that I am using MS Excel 2007.

 

[FKoenig]

If your accounts are always multiples of 100, then you can use a Vlookup. For instance, =VLOOKUP(C11,$B$2:$G$7,(D11/100)+1,0)

Alternatively, you would need to creating a lookup table for your column IDs and then use two nested vlookups.

New range "accounts"

<TABLE style="WIDTH: 99pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=131 border=0><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=82 height=20>100</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>200</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>300</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>400</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>500</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>6</TD></TR></TBODY></TABLE>

=VLOOKUP(C11,$B$2:$G$7,VLOOKUP(D11,accounts,2,0),0)

 

[eduardo1966]

Unfortunately my department numbers can be any three-digit combination. (The dataset is much larger than the example I have given.) Also, I don't think the vlookup approach is going to get me to the single-cell solution, and I don't think it would work where the account number is repeated as in rows 13 and 14. But thanks for trying.

 

[circledchicken]

Hi,

Perhaps try the following (its an array formula so enter with CTRL-SHIFT-ENTER):


=SUM(TRANSPOSE(INDIRECT(ADDRESS(MATCH(C11:C16,$A$3:$A$7,0)+2,MATCH(D11:D16,$B$2:$F$2,0)+1))))

• The first MATCH returns the rows that match the Account criteria (+2 as the data starts in row 3).
  
• The second MATCH returns the columns that match the Department criteria (+1 as the data starts in column 2).
  
• The ADDRESS function takes the rows and columns as inputs and converts them into cell references.
  
• The INDIRECT function converts the cell references to the values they hold.
  
• Finally, the TRANSPOSE function converts the array to a single row array which we can then SUM.

 

[eduardo1966]

Thanks, circledchicken! That works brilliantly. I had never used the "address" function before, but now I see where it can be useful.

To simplify the description of my task initially, I combined the two tables on one example worksheet. Since my raw data will come from an Oracle report and will change each month, I need the tables in two places in the same file. Do you know if it is possible to make the "[sheet_text]" parameter work when the address function is buried in an array formula such as the one you designed?

I was abolutely able to get your solution to work when I placed it in the same sheet as the data, but when I moved it to another worksheet, I wan't able to make it produce a value.

Many thanks,

eduardo1966

 

[mgirvin]

That is an absolutely beautiful formula, circledchicken!

I have never run into a situation where you had to do "two-way lookup adding", but your formula does the trick! Very clever!

Here is a variation on your formula that gets rid of the + 2 and +1 by simply extending the ranges in MATCH:

=SUM(TRANSPOSE(INDIRECT(ADDRESS(MATCH(C11:C16,A1:A7,0),MATCH(D11:D16,A2:F2,0)))))

I played around with your idea and I am left wondering why the INDEX could not handle the arrays created by the MATCH functions, something like this:

=SUM(INDEX(B3:F7,MATCH(C11:C16,A3:A7),MATCH(D11:D16,B2:F2)))

Do you know? Or does anyone know?

 

[Arul.rajesh]

<CODE>
Option Base 1
Function TwoWay(ByVal Dat As Range, ByVal Req As Range)
Dim Datr As Variant
Dim Reqr As Variant
Set w = Application.WorksheetFunction
'\\ LOADING THE RANGES TO ARRAYS
Datr = Dat.Value
Reqr = Req.Value
numcols = UBound(w.Transpose(Datr))
numrows = UBound(Datr)
LoopNum = UBound(Reqr)
FINALSUM = 0
'\\ HAVING FUN WITH LOOPS
For i = 2 To LoopNum
For J = 3 To numrows
If Reqr(i, 1) = Datr(J, 1) Then
For K = 2 To numcols
If Reqr(i, 2) = Datr(2, K) Then
FINALSUM = FINALSUM + Datr(J, K)
End If
Next K
End If
Next J
Next i
'\\ FINISHED FUNWITH LOOPS
TwoWay = FINALSUM
End Function
</CODE>




In your example the function would be =TwoWay(A1:F7,C10:D16)
Let the ranges be anywhere.

 

[Yahya]

That is an absolutely beautiful formula, circledchicken!

I have never run into a situation where you had to do "two-way lookup adding", but your formula does the trick! Very clever!

Here is a variation on your formula that gets rid of the + 2 and +1 by simply extending the ranges in MATCH:

=SUM(TRANSPOSE(INDIRECT(ADDRESS(MATCH(C11:C16,A1:A7,0),MATCH(D11:D16,A2:F2,0)))))

I played around with your idea and I am left wondering why the INDEX could not handle the arrays created by the MATCH functions, something like this:

=SUM(INDEX(B3:F7,MATCH(C11:C16,A3:A7),MATCH(D11:D16,B2:F2)))

Do you know? Or does anyone know?

it is interesting issue

 

[eduardo1966]

Mike,

The second formula in your post uses one of the approaches that I had tried before reaching out. It seems as if it should work. Maybe there's a developer out there who can chime in.

Thanks,

eduardo1966