| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF to calculate units sold in a specified timeframe
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. -- TIA, Nan |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF to calculate units sold in a specified timeframe
=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) "Nan" wrote in message ... 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. -- TIA, Nan |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF to calculate units sold in a specified timeframe
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". -- TIA, Nan "Bob Phillips" wrote: =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) "Nan" wrote in message ... 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. -- TIA, Nan |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF to calculate units sold in a specified timeframe
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) "Nan" wrote in message ... 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". -- TIA, Nan "Bob Phillips" wrote: =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) "Nan" wrote in message ... 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. -- TIA, Nan |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF to calculate units sold in a specified timeframe
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,"Appl es",b1:b10) ColA ColB apples 1 carrots 2 apples 5 oranges 1 celery 2 apples 1 onions 2 apples 5 oranges 1 celery 2 -- TIA, Nan "Bob Phillips" wrote: 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) "Nan" wrote in message ... 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". -- TIA, Nan "Bob Phillips" wrote: =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) "Nan" wrote in message ... 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. -- TIA, Nan |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF to calculate units sold in a specified timeframe
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) "Nan" wrote in message ... 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,"Appl es",b1:b10) ColA ColB apples 1 carrots 2 apples 5 oranges 1 celery 2 apples 1 onions 2 apples 5 oranges 1 celery 2 -- TIA, Nan "Bob Phillips" wrote: 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) "Nan" wrote in message ... 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". -- TIA, Nan "Bob Phillips" wrote: =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) "Nan" wrote in message ... 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. -- TIA, Nan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula to track timeframe by automatically highlighting cells | Excel Worksheet Functions | |||
| counting dates within a timeframe | Setting up and Configuration of Excel | |||
| Numeric Rank By Qty Sold | Excel Discussion (Misc queries) | |||
| keep track of gift certificates sold | New Users to Excel | |||
| Secondary Axis Annual Units sold on 1 Y Axis - Dollar Volume on another | Charts and Charting in Excel | |||
Linear Mode
