Help with looping thru columns

[Chris-KPMG]

I trying to automate a report that is ran in Cognos, and extracted to
an excel file. I am almost complete, but am having trouble with
totaling coulmuns.

Basically, There are 7 columns which are to be totaled. My limitation
is this:
IF column 'D' is = "G" then loop thru Rows and Columns to process G
Totals Then
IF column 'D' is = "N" then loop thru Rows & Columns to Process
N Totals Then
Total the Totals for "G" and "N";

Example:

A B C D E F G H I J Total
2915R NOYR 131000 G 0.00 (470.28) 0.00 0.00 88.28 0.00 <Total>
3220R NOYR 131000 G 0.00 (231.57) 0.00 0.00 214.66 0.00 <Total>
0800A 2004 131009 G 0.00 622.64 0.00 0.00 0.00 0.00 <Total>
Sector G Totals ---- ------ ---- ---- ---- ---- -----
<Total><Total><Total><Total><Total> <Total>

2915R NOYR 131000 N 0.00 (470.28) 0.00 0.00 88.28 0.00 <Total>
3220R NOYR 131000 N 0.00 (231.57) 0.00 0.00 214.66 0.00 <Total>
0800A 2004 131009 N 0.00 622.64 0.00 0.00 0.00 0.00 <Total>
Sector N Totals ---- ------ ---- ---- ---- ---- -----
<Total><Total><Total><Total><Total> <Total>
---- ------ ---- ---- ---- ---- -----
Grand Total <Total><Total><Total><Total><Total> <Total>

 

[Dave Peterson]

If your range is sorted by column D (Ns and Gs), it sure looks like
Data|subtotal would work ok.

 

[Earl Kiosterud]

Chris,

Dave's sort, then subtotal is a solution. It requires you do those steps
each time the data changes. You could also use a pivot table, though it too
would have to be refreshed any time the data has changed.

For a totally dynamic solution, you could use a DSUM function for the G
stuff, and a DSUM for the N stuff. You can do this since you know in
advance that there are D and G categories. When the data changes, the
totals will change.