Comparing figures over 6 months (SUMPRODUCT)

[buffalobill]

Hi All,

I need to compare figures for the past 6 months, for the same period that we are in in the current month i.e if 20 days have elapsed this month I want to add all the figures for the first 20 days of April, March etc. I tried using SUMPRODUCT but cant seem to get it to work, should I be using something else?

I have attached a sheet with the way my data is set out, only working days are shown on the sheet so a month will not neccessaraly start from the 1st, any help will be greatly appreciated

[teylyn]

For April

=SUM(OFFSET($C$1,MATCH("apr,10",D:D,0),0,$I$8,1))

For March

=SUM(OFFSET($C$1,MATCH("mar,10",D:D,0),0,$I$8,1))

[buffalobill]

Thank you, the figures dont seem to add up when I put the formula in though, for April the actual amount is 899 but the formula gives me 888 and for March the actual is 1016 but the formula gives me 1053. Any suggestions or am I doing something wrong?

[teylyn]

slight oversight in the offset start. try

April
=SUM(OFFSET($C$1,MATCH("apr,10",D:D,0)-1,0,$I$8,1))

March
=SUM(OFFSET($C$1,MATCH("mar,10",D:D,0)-1,0,$I$8,1))

[buffalobill]

Works perfectly, thank you very much.