SUMIF with Dynamic Array/range

[Cet88]

HI,

I have a fixed asset register set up as follows:

Cell A1 date
Col A - Location
Col N to Y - Jan to Dec. Each column contains the monthly depreciation value for each asset.

I need to do a sum based on the location, but I need the sum range to change to give me the YTD values. so for example in Jan I need it to sum just col N, but in March it should sum values in col N to P that match with the location criteria.

I started with various nested Index/Match then changed to a sumproduct but I can't get it to work. Any ideas?

thanks

 

[CodeNinja]

Cet88,
Having a little trouble picturing your spreadsheet... In cell A1 you have a date, but column A is location? Is row 1 just headers and A1 is the date then A2:Axxxx is a location? If this is the case, are you trying to sum numbers in N-Y based on 2 conditions... 1 being the location is "X" and 2 being the month is less than the date?

IF So...
You can use sumproduct to get your answer...

=Sumproduct((condition1)*(condition2)*(range))
IE:
=SUMPRODUCT((N2:Y500)*(MONTH(A1)>=COLUMN(N1:Y1)-13)*(A2:A500="X"))

If that is not the case, please provide a sample of what your data should look like.

Thanks,

CN.