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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BROCK8292 - 25 Mar 2008 00:59 GMT

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

Reply to this Message

Pete_UK - 25 Mar 2008 01:39 GMT

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 <BROCK8...@discussions.microsoft.com>
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

Reply to this Message

Pete_UK - 25 Mar 2008 02:00 GMT

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

On Mar 24, 11:59 pm, BROCK8292 <BROCK8...@discussions.microsoft.com>
wrote:

Reply to this Message