Rolling 30 days... Different rows

**diggy13** · 06-30-2010, 01:57 AM

Hi,

Im trying to set up a rolling 30 day variance tracker. I want my months set up in there own row.

I used the formula =SUMIF($B$3:B3,">" &B3-30,$B$4:B4) for the first row. Is there a way to continue this in different row.

I'll attach the begining of my work book.

Cheers,

**DonkeyOte** · 06-30-2010, 04:29 AM

Given your layout and the fact that the file is XL2007+ format you might find you can use SUMIFS

B5:
=SUMIFS($B$4:$AF$8,$B$3:$AF$7,">"&B3-30,$B$3:$AF$7,">=2010-01-01",$B$3:$AF$7,"<="&B3)

you should find the above can be applied to all Total cells (row 5 & row 9) without need for modification.

note: SUMIFS is not backwards compatible with earlier versions.

**diggy13** · 06-30-2010, 11:31 AM

Thanks for the reply,

I cant get this formula to work. but in theory could this formula be applied to a full calander year ?

Also what happens if its opened in a XL2003 ? it function properly

Cheers,

**DonkeyOte** · 06-30-2010, 03:46 PM

Apologies - I was being a little dense.

The formula should be... for XL2007 and beyond:

B5:
=IF(B3="","",SUMIFS($B$4:$AF4,$B$3:$AF3,">"&B3-30,$B$3:$AF3,">=2010-01-01",$B$3:$AF3,"<="&B3))
copied to AF5 and in turn B9:AF9

Pre XL2007 you would realistically either be looking at a SUMPRODUCT or perhaps preferable to use 2 SUMIFs:

B5:
=IF(B3="","",SUMIF($B$3:$AF3,">"&B3-30,$B$4:$AF4)-SUMIF($B$3:$AF3,">"&B3,$B$4:$AF4))
applied in the same way as per the SUMIFS function