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" wrote:

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" wrote:
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.