Sum all values that occur in a specified year

**redsox0407** · 03-14-2012, 01:08 PM

I have 3 tables (titled Fund1, Fund2, Fund3) with columns for cap call and distribution data and the dates. In a summation table below, I'm trying to write formulas for combined calls and distributions per year. The formula for cap calls in 2001 would theoretically read =SUM(Fund1[Cap Calls] in 2001)+SUM(Fund2[Cap Calls] in 2001)+SUM(Fund3[Cap Calls] in 2001). What I'm trying to do is have a formula in say C55 that would maybe incorporate multiple SUMIF formulas that say "sum values in Fundx[Cap Calls] if they fall on a date in the same year as B55." The dates are quarter end, so they're fixed interval. This is for a workbook template, and excess table space will be deleted, so I'm really trying to tie in the "year" cell in the summation table to avoid problems down the road. Thanks.

Attached includes sample data.

**DGagnon** · 03-14-2012, 01:19 PM

you will need to fix the value errors in your 3rd table, but this should work for you

=SUMIF(Fund1[InvDate],"*"&RIGHT(Table5[[#This Row],[Year]],2),Fund1[Cap Calls])+SUMIF(Table3[InvDate],"*"&RIGHT(Table5[[#This Row],[Year]],2),Table3[Cap Calls])+SUMIF(Table4[InvDate],"*"&RIGHT(Table5[[#This Row],[Year]],2),Table4[Cap Calls])