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

  • Thread starter Thread starter BROCK8292
  • Start date Start date
B

BROCK8292

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
 
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
 
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$50>C1-7)*(B$2:B$50))

Hope this helps.

Pete
 
Back
Top