# SUMIF to calculate units sold in a specified timeframe

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.

B

#### Bob Phillips

=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)

N

#### Nan

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".

B

#### Bob Phillips

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)

N

#### Nan

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

B

#### Bob Phillips

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)