SUMIF to calculate units sold in a specified timeframe

  • Thread starter Thread starter Nan
  • Start date Start date
N

Nan

I could use your help to sum a range if a condition is met. I'm trying to
calculate the number of units sold in the last 6 months and the last 8 weeks.

Here's my worksheet layout:

C1: =now()

A10:C22 as follows
Col A Col B Col C
Jan-2007 2007 2
Feb-2007 2007 -1
Mar-2007 2007 0
Apr-2007 2007 0
May-2007 2007 0
Jun-2007 2007 0
Jul-2007 2007 0
Aug-2007 2007 0
Sep-2007 2007 0
Oct-2007 2007 1
Nov-2007 2007 0
Dec-2007 2007 0
Jan-2008 2008 0

B38: =DATE(YEAR($C$1),MONTH($C$1)-6,DAY(1)) -- 6 months from today
B39: =DATE(YEAR($C$1),MONTH($C$1)-2,DAY(1)) -- 2 months from today

I'm stuck trying to evaluate the condition, then summing only those units
sold in Col C within the specified time frame.
 
=SUMIF(A:A,">="&BE38,C:C)

and

=SUMIF(A:A,">="&BE39,C:C)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Gosh, such an easy solution! Thank you so much!

I'll be posting another question soon regarding using SUMIF with an "OR"
condition, i.e. sum the range if col A contains "apple" or col A contains
"orange".
 
That is done with

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A20,{"apple","orange"},0))))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Thanks, Bob. I really appreciate your time. SUMPRODUCT is new to me. I
tried your solution with my worksheet, but I realize I wasn't clear on the
setup. I don't understand where SUMPRODUCT actually is told what range to
calculate. But, before you spend any more time on this, I did post another
question which was answered by Marcelo. I used his suggested SUMIF answer:

=SUMIF(a1:a10,"oranges",b1:b10)+SUMIF(a1:a10,"Apples",b1:b10)


ColA ColB
apples 1
carrots 2
apples 5
oranges 1
celery 2
apples 1
onions 2
apples 5
oranges 1
celery 2
 
I failed to give you a sum, I gave you a count.

Sum would be

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,{"apple","orange"},0))),B1:B10)

Marcelo's is probably better in this particular instance, but mine is more
flexible, so put it in your toolbox.

Also see http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top