Factorial (like =FACT) function?

Maybe something like this:

For a data list in A1:C100
Col_A contains dates and a column title in A1
Col_B contains products and a column title in B1
Col_C contains amounts and a column title in C1

This formula sums all of YTD amounts (through the current month) where the
Prod="X"

D1:
=SUMPRODUCT((B2:B100="x")*(YEAR(A2:A100)=YEAR(TODAY()))*(MONTH(A2:A100)<=MONTH(TODAY()))*C2:C100)

Note: in case text wrap impacts the display, there are no spaces in that
formula.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"mr tom" wrote:

> Not quite.
>
> Another poorly asked question on my part!
>
> Let's say you have a data source where one column is date. Another column
> might be product. Another might be amount.
>
> At the end of the month, you'd sum all the amounts where the product meets
> certain criterea and MONTH(date entered in column) = MONTH(TODAY()).
>
> This is how it presently works.
>
> If I then wanted year to date, I could simply sum all cases for that year,
> but let's say the data quality is not what it could be and having a series of
> months that don't add up to the year to date total is a little embarrassing.
>
> My alternative was a function that does
> {=SUM(IF(Product="x"),IF(MONTH(DateEntered)=MONTH(TODAY()),Amounts,0),0)}
> [the current formula or close to it]
> but does this for the current month and every previous month until Month=0.
>
> Am I clutching at straws?
>
> "Ron Coderre" wrote:
>
> > Try this:
> >
> > For a value in A1
> >
> > This formula returns the sum of all numbers from 1 thru the value in A1
> > B1: =(A1+1)*(A1/2)
> >
> > Does that help?
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "mr tom" wrote:
> >
> > > A factorial expression, e.g. 5! is evaluated 5*4*3*2*1.
> > >
> > > In Excel, this can be expressed as =FACT(5)
> > >
> > > I want to do something similar, but a little different: 5+4+3+2+1
> > >
> > > E.g. Year to date could be results for June + May + April + March + February
> > > + January, where Month(TODAY) gives 06 as June and then simply recalculates
> > > the month sensitive formulas based on each month below this number, returning
> > > the total of all evaluations.
> > >
> > > Ideally I'd like to manage this within a formula, without resorting to VBA.
> > > Any ideas?
> > >
> > > Tom.
> > >
> > > P.S. Many thanks for any attempts - regardless of whether they solve my
> > > problem!

Yes - that's exactly it. I'll stick with my previous style of formula but
you're absolutely right - I deserve to be taken out and shot for not thinking
of a <=.

I've clearly overcomplicated things - your solution will work a treat.

Many thanks,

Tom.

"Ron Coderre" wrote:

> Maybe something like this:
>
> For a data list in A1:C100
> Col_A contains dates and a column title in A1
> Col_B contains products and a column title in B1
> Col_C contains amounts and a column title in C1
>
> This formula sums all of YTD amounts (through the current month) where the
> Prod="X"
>
> D1:
> =SUMPRODUCT((B2:B100="x")*(YEAR(A2:A100)=YEAR(TODAY()))*(MONTH(A2:A100)<=MONTH(TODAY()))*C2:C100)
>
> Note: in case text wrap impacts the display, there are no spaces in that
> formula.
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "mr tom" wrote:
>
> > Not quite.
> >
> > Another poorly asked question on my part!
> >
> > Let's say you have a data source where one column is date. Another column
> > might be product. Another might be amount.
> >
> > At the end of the month, you'd sum all the amounts where the product meets
> > certain criterea and MONTH(date entered in column) = MONTH(TODAY()).
> >
> > This is how it presently works.
> >
> > If I then wanted year to date, I could simply sum all cases for that year,
> > but let's say the data quality is not what it could be and having a series of
> > months that don't add up to the year to date total is a little embarrassing.
> >
> > My alternative was a function that does
> > {=SUM(IF(Product="x"),IF(MONTH(DateEntered)=MONTH(TODAY()),Amounts,0),0)}
> > [the current formula or close to it]
> > but does this for the current month and every previous month until Month=0.
> >
> > Am I clutching at straws?
> >
> > "Ron Coderre" wrote:
> >
> > > Try this:
> > >
> > > For a value in A1
> > >
> > > This formula returns the sum of all numbers from 1 thru the value in A1
> > > B1: =(A1+1)*(A1/2)
> > >
> > > Does that help?
> > > ***********
> > > Regards,
> > > Ron
> > >
> > > XL2002, WinXP
> > >
> > >
> > > "mr tom" wrote:
> > >
> > > > A factorial expression, e.g. 5! is evaluated 5*4*3*2*1.
> > > >
> > > > In Excel, this can be expressed as =FACT(5)
> > > >
> > > > I want to do something similar, but a little different: 5+4+3+2+1
> > > >
> > > > E.g. Year to date could be results for June + May + April + March + February
> > > > + January, where Month(TODAY) gives 06 as June and then simply recalculates
> > > > the month sensitive formulas based on each month below this number, returning
> > > > the total of all evaluations.
> > > >
> > > > Ideally I'd like to manage this within a formula, without resorting to VBA.
> > > > Any ideas?
> > > >
> > > > Tom.
> > > >
> > > > P.S. Many thanks for any attempts - regardless of whether they solve my
> > > > problem!

Thanks for the feed back...I'm glad you got that to work for you.

BTW...this newsgroup hardly ever shoots anybody any more. <vbg>

***********
Regards,
Ron

XL2002, WinXP


"mr tom" wrote:

> Yes - that's exactly it. I'll stick with my previous style of formula but
> you're absolutely right - I deserve to be taken out and shot for not thinking
> of a <=.
>
> I've clearly overcomplicated things - your solution will work a treat.
>
> Many thanks,
>
> Tom.
>
> "Ron Coderre" wrote:
>
> > Maybe something like this:
> >
> > For a data list in A1:C100
> > Col_A contains dates and a column title in A1
> > Col_B contains products and a column title in B1
> > Col_C contains amounts and a column title in C1
> >
> > This formula sums all of YTD amounts (through the current month) where the
> > Prod="X"
> >
> > D1:
> > =SUMPRODUCT((B2:B100="x")*(YEAR(A2:A100)=YEAR(TODAY()))*(MONTH(A2:A100)<=MONTH(TODAY()))*C2:C100)
> >
> > Note: in case text wrap impacts the display, there are no spaces in that
> > formula.
> >
> > Does that help?
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "mr tom" wrote:
> >
> > > Not quite.
> > >
> > > Another poorly asked question on my part!
> > >
> > > Let's say you have a data source where one column is date. Another column
> > > might be product. Another might be amount.
> > >
> > > At the end of the month, you'd sum all the amounts where the product meets
> > > certain criterea and MONTH(date entered in column) = MONTH(TODAY()).
> > >
> > > This is how it presently works.
> > >
> > > If I then wanted year to date, I could simply sum all cases for that year,
> > > but let's say the data quality is not what it could be and having a series of
> > > months that don't add up to the year to date total is a little embarrassing.
> > >
> > > My alternative was a function that does
> > > {=SUM(IF(Product="x"),IF(MONTH(DateEntered)=MONTH(TODAY()),Amounts,0),0)}
> > > [the current formula or close to it]
> > > but does this for the current month and every previous month until Month=0.
> > >
> > > Am I clutching at straws?
> > >
> > > "Ron Coderre" wrote:
> > >
> > > > Try this:
> > > >
> > > > For a value in A1
> > > >
> > > > This formula returns the sum of all numbers from 1 thru the value in A1
> > > > B1: =(A1+1)*(A1/2)
> > > >
> > > > Does that help?
> > > > ***********
> > > > Regards,
> > > > Ron
> > > >
> > > > XL2002, WinXP
> > > >
> > > >
> > > > "mr tom" wrote:
> > > >
> > > > > A factorial expression, e.g. 5! is evaluated 5*4*3*2*1.
> > > > >
> > > > > In Excel, this can be expressed as =FACT(5)
> > > > >
> > > > > I want to do something similar, but a little different: 5+4+3+2+1
> > > > >
> > > > > E.g. Year to date could be results for June + May + April + March + February
> > > > > + January, where Month(TODAY) gives 06 as June and then simply recalculates
> > > > > the month sensitive formulas based on each month below this number, returning
> > > > > the total of all evaluations.
> > > > >
> > > > > Ideally I'd like to manage this within a formula, without resorting to VBA.
> > > > > Any ideas?
> > > > >
> > > > > Tom.
> > > > >
> > > > > P.S. Many thanks for any attempts - regardless of whether they solve my
> > > > > problem!

Not since the last time, anyway.

"Ron Coderre" wrote:

> Thanks for the feed back...I'm glad you got that to work for you.
>
> BTW...this newsgroup hardly ever shoots anybody any more. <vbg>
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "mr tom" wrote:
>
> > Yes - that's exactly it. I'll stick with my previous style of formula but
> > you're absolutely right - I deserve to be taken out and shot for not thinking
> > of a <=.
> >
> > I've clearly overcomplicated things - your solution will work a treat.
> >
> > Many thanks,
> >
> > Tom.
> >
> > "Ron Coderre" wrote:
> >
> > > Maybe something like this:
> > >
> > > For a data list in A1:C100
> > > Col_A contains dates and a column title in A1
> > > Col_B contains products and a column title in B1
> > > Col_C contains amounts and a column title in C1
> > >
> > > This formula sums all of YTD amounts (through the current month) where the
> > > Prod="X"
> > >
> > > D1:
> > > =SUMPRODUCT((B2:B100="x")*(YEAR(A2:A100)=YEAR(TODAY()))*(MONTH(A2:A100)<=MONTH(TODAY()))*C2:C100)
> > >
> > > Note: in case text wrap impacts the display, there are no spaces in that
> > > formula.
> > >
> > > Does that help?
> > > ***********
> > > Regards,
> > > Ron
> > >
> > > XL2002, WinXP
> > >
> > >
> > > "mr tom" wrote:
> > >
> > > > Not quite.
> > > >
> > > > Another poorly asked question on my part!
> > > >
> > > > Let's say you have a data source where one column is date. Another column
> > > > might be product. Another might be amount.
> > > >
> > > > At the end of the month, you'd sum all the amounts where the product meets
> > > > certain criterea and MONTH(date entered in column) = MONTH(TODAY()).
> > > >
> > > > This is how it presently works.
> > > >
> > > > If I then wanted year to date, I could simply sum all cases for that year,
> > > > but let's say the data quality is not what it could be and having a series of
> > > > months that don't add up to the year to date total is a little embarrassing.
> > > >
> > > > My alternative was a function that does
> > > > {=SUM(IF(Product="x"),IF(MONTH(DateEntered)=MONTH(TODAY()),Amounts,0),0)}
> > > > [the current formula or close to it]
> > > > but does this for the current month and every previous month until Month=0.
> > > >
> > > > Am I clutching at straws?
> > > >
> > > > "Ron Coderre" wrote:
> > > >
> > > > > Try this:
> > > > >
> > > > > For a value in A1
> > > > >
> > > > > This formula returns the sum of all numbers from 1 thru the value in A1
> > > > > B1: =(A1+1)*(A1/2)
> > > > >
> > > > > Does that help?
> > > > > ***********
> > > > > Regards,
> > > > > Ron
> > > > >
> > > > > XL2002, WinXP
> > > > >
> > > > >
> > > > > "mr tom" wrote:
> > > > >
> > > > > > A factorial expression, e.g. 5! is evaluated 5*4*3*2*1.
> > > > > >
> > > > > > In Excel, this can be expressed as =FACT(5)
> > > > > >
> > > > > > I want to do something similar, but a little different: 5+4+3+2+1
> > > > > >
> > > > > > E.g. Year to date could be results for June + May + April + March + February
> > > > > > + January, where Month(TODAY) gives 06 as June and then simply recalculates
> > > > > > the month sensitive formulas based on each month below this number, returning
> > > > > > the total of all evaluations.
> > > > > >
> > > > > > Ideally I'd like to manage this within a formula, without resorting to VBA.
> > > > > > Any ideas?
> > > > > >
> > > > > > Tom.
> > > > > >
> > > > > > P.S. Many thanks for any attempts - regardless of whether they solve my
> > > > > > problem!