Summing a crosstab

D

dysolomon

Is there a way to do a sumif on a crosstab?

For example:

Issue Year
Duration 2,004 2,005 2,006 2,007 2,008 2,009
0 453 - - - - 505
1 - - 789 498 -
2 650 - - -
3 5,496 1,086 2,210
4 - 6,241
5 13,664

I want to summarize by Incurred Year, which would be Issue year + Duration

So for Incurred Year 2009, I want to sum all cells where the Issue Year +
Duration = 2009. sumif (row + col = year)

Incurred Year Amount
2009 22,621
2008 1,584
2007 6,285
2006 650
2005 -
2004 453


I don't know whether to use sumif, match, lookup, or if it is even possible.

Thanks.
 
M

Max

Here's one relatively simple play which delivers it ..

Assume your source table as posted is in A1:G8
where in B2:G2 are the years: 2004, 2005 ... 2009
and in A3:A8 are the durations: 0, 1,... 5

Create a DV in say, cell B10 to select the year (from B2:G2)
[Click Data > Validation, Allow: List, Source: =$B$2:$G$2]
Then place this in B11:
=IF(ROWS($1:1)>MATCH($B$10,$B$2:$G$2,0),"",OFFSET($A$2,MATCH($B$10,$B$2:$G$2,0)+1-ROWS($1:1),ROWS($1:1)))
Copy B11 down to B16 (As the source is a 6 x 6 table, hence copy down 6
cells to cover the max results range). This is the key formula which strips
the diagonal cells from the source table depending on the year selected in
B10
Then in B17: =SUM(B11:B16) will yield the desired result
Inspiring? hit the YES below
 
D

dysolomon

This is a great process and it worked great, but not exactly what I need.

I would like to have all of the totals without having to choose the year.
That way, I could populate the results on a summary tab.

Max said:
Here's one relatively simple play which delivers it ..

Assume your source table as posted is in A1:G8
where in B2:G2 are the years: 2004, 2005 ... 2009
and in A3:A8 are the durations: 0, 1,... 5

Create a DV in say, cell B10 to select the year (from B2:G2)
[Click Data > Validation, Allow: List, Source: =$B$2:$G$2]
Then place this in B11:
=IF(ROWS($1:1)>MATCH($B$10,$B$2:$G$2,0),"",OFFSET($A$2,MATCH($B$10,$B$2:$G$2,0)+1-ROWS($1:1),ROWS($1:1)))
Copy B11 down to B16 (As the source is a 6 x 6 table, hence copy down 6
cells to cover the max results range). This is the key formula which strips
the diagonal cells from the source table depending on the year selected in
B10
Then in B17: =SUM(B11:B16) will yield the desired result
Inspiring? hit the YES below
--
Max
Singapore
---
dysolomon said:
Is there a way to do a sumif on a crosstab?

For example:

Issue Year
Duration 2,004 2,005 2,006 2,007 2,008 2,009
0 453 - - - - 505
1 - - 789 498 -
2 650 - - -
3 5,496 1,086 2,210
4 - 6,241
5 13,664

I want to summarize by Incurred Year, which would be Issue year + Duration

So for Incurred Year 2009, I want to sum all cells where the Issue Year +
Duration = 2009. sumif (row + col = year)

Incurred Year Amount
2009 22,621
2008 1,584
2007 6,285
2006 650
2005 -
2004 453


I don't know whether to use sumif, match, lookup, or if it is even possible.

Thanks.
Click to expand...
Click to expand...
 
M

Max

A slight twist to the earlier. You could drop the DV in B10 and just use the
year headers in B2:G2 instead to derive the entire lot at one go.
In B11
=IF(ROWS($1:1)>COLUMNS($A:A),"",OFFSET($A$2,COLUMNS($A:A)+1-ROWS($1:1),ROWS($1:1)))
Copy down to B16
In B17: =SUM(B11:B16)
Select B11:B17, copy across to G17. B17:G17 gives the results.
Joy? hit the YES below. Above and earlier formulas route works in any
version of Excel.
 
M

Max

Just posted this, crossed with your reply ...

A slight twist to the earlier. You could drop the DV in B10 and just use the
year headers in B2:G2 instead to derive the entire lot at one go.
In B11
=IF(ROWS($1:1)>COLUMNS($A:A),"",OFFSET($A$2,COLUMNS($A:A)+1-ROWS($1:1),ROWS($1:1)))
Copy down to B16
In B17: =SUM(B11:B16)
Select B11:B17, copy across to G17. B17:G17 gives the results.
Joy? hit the YES below. Above and earlier formulas route works in any
version of Excel.
 
M

minyeh

Just posted this, crossed with your reply ...

A slight twist to the earlier. You could drop the DV in B10 and just use the
year headers in B2:G2 instead to derive the entire lot at one go.
In B11:
=IF(ROWS($1:1)>COLUMNS($A:A),"",OFFSET($A$2,COLUMNS($A:A)+1-ROWS($1:1),ROWS­($1:1)))
Copy down to B16
In B17: =SUM(B11:B16)
Select B11:B17, copy across to G17.  B17:G17 gives the results.
Joy? hit the YES below. Above and earlier formulas route works in any
version of Excel.
--
Max
Singapore
---






- Show quoted text -
Click to expand...

Using array formula would be a great help:
The data u provide is in A1:G7, my formula for year 2004 total is in
cell B8
=SUMPRODUCT(--(ROW($B$2:$G$7)-ROW($B$2)+$B$1:$G$1=B$1),$B$2:$G$7)
copy across for all the remaining years up to cell G8
it's easier to see in excel for the range used, note the $ sign for
fixed cell and non-fixed cell.

Hope this helps, straight forward use of sumproduct and less confusing
for crosstab summing.


Min Yeh
 
Top