last year.......................................thisyea r
A....55..........................................A .....333
B....77........................................... D.....444
C....88..........................................A ......666

COMBINE THE 2


A....55.......YEAR1
B....77.......YEAR1
C....88......YEAR1
A.....333.....YEAR2
D.....444......YEAR2
A......666......YEAR2

Now use a sumproduct formula

=sumproduct(($a$1:$a$6="A")*($c$1:$c$6="year2")*($ b$1:$b$6))

this gives you the A totals for year2 = 999

you can make a table with A,B,C,D IN CELLS A2:A5 AND YEAR1 IN CELL B1
AND YEAR2 IN CELL C1. Then let your sumproduct formula reference the
row and column headings to pull all the totals for all the years. Much
better, in my opinion, than a pivot table.

in B2 the formula would change by the A in the first part of the
formula would be replaced by $a2, year1 by b$1 and year2 by c$1

now copy it across and down


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=574143