Sum cells only if 6 weeks before todays date

[JamesMook]

I'm looking for a formula that will sum together cells in a column but only if the data comes from 6 weeks before todays date.

The column with data would be B1:B999
The column with dates would be A1:A999

I'm presuming it would be something like =SUMIF(B1:B999,"<"&TODAY(),A1:A999) however this doesn't specify 6 weeks only. Any one have any ideas?

Thanks

 

[gaz_chops]

When you say "only if the data comes from 6 weeks before todays date."
Do you mean data for the LAST 6 weeks?

 

[Chrisdontm]

Would this get you what you are looking for...?
=SUMIF(A1:A999,">"&(TODAY()-42),B1:B999)

 

[JamesMook]

Yes that's correct gaz_chops

 

[JamesMook]

Would this get you what you are looking for...?
=SUMIF(A1:A999,">"&(TODAY()-42),B1:B999)

I'll give this a go and let you know but definately looks like it should work. Don't know how i didn't work that out, must be that friday feeling. thanks

 

[gaz_chops]

In that case the formula provided by Chrisdontm should work.

 

[JamesMook]

That formula works great thanks. I'm not sure if oi need to start a new thread for this but as it was linked i thought i'd post here. I now want to combine these2 formula. Happy to start a new thread if required.

=SUMIFS(RAW!J:J,RAW!I:I,"James Pite",RAW!B:B,"2015") (i want to add the within 6 weeks part of the below formula)

=SUMIFS(RAW!K:K,RAW!A:A,">="&B2,RAW!A:A,"<="&B3)


My attempt was as follows but contains an error.

=SUMIFS(RAW!A:A,">"&(TODAY()-42,RAW!J:J,RAW!I:I,Joe Bloggs",RAW!B:B,"2015")


Thanks

 

[gaz_chops]

Which range are you trying to sum? Also, you should avoid referencing whole columns, try & reduce it to your actual range.

 

[JamesMook]

The formula should sum RAW!A:A.

I understand whole columns is not ideal however over the year there will be alot of a data coming in from a data source and therefore the range would effectively change and would not want the formuala to 'Break' so to speak.

 

[gaz_chops]

The formula should sum RAW!A:A.

I understand whole columns is not ideal however over the year there will be alot of a data coming in from a data source and therefore the range would effectively change and would not want the formuala to 'Break' so to speak.

I thought Col A was your dates? Can you post a sample of your data, check my footnote for options to do this.