is there a formula to add numbers in a range based on several crit
 

im trying to add the quantities in one column based on if the dates in the
corresponding cells in another column occured within the past week
ive tried a nested sumif formula and a dsum but neither work very well
thank you,
barry

is there a formula to add numbers in a range based on severalcrit
 

Suppose you have dates in column A and quantities in column B in rows
2 to 50. Then you can try this formula in D1:

=SUMIF(A$2:A$50,"<="&TODAY(),B$2:B$50) - SUMIF(A$2:A$50,"<"&TODAY()-7,B
$2:B$50)

if you want it to compare with today's date. However, this will change
when you open the file on a different day, so you might like to put a
reference date in C1 (which could be =TODAY() ), and then make the
formula:

=SUMIF(A$2:A$50,"<="&C1,B$2:B$50) - SUMIF(A$2:A$50,"<"&C1-7,B$2:B$50)

Hope this helps.

Pete

On Mar 24, 11:59*pm, BROCK8292
wrote:
im trying to add the quantities in one column based on if the dates in the
corresponding cells in another column occured within the past week
ive tried a nested sumif formula and a dsum but neither work very well
thank you,
barry

is there a formula to add numbers in a range based on severalcrit
 

Hi Barry,

here's another way (same set up as before, with reference date in C1
and formula in D1):

=SUMPRODUCT((A$2:A$50<=C1)*(A$2:A$50C1-7)*(B$2:B$50))

Hope this helps.

Pete

On Mar 24, 11:59*pm, BROCK8292
wrote:
im trying to add the quantities in one column based on if the dates in the
corresponding cells in another column occured within the past week
ive tried a nested sumif formula and a dsum but neither work very well
thank you,
barry