Hi Bob,
Thanks for the reply. I am trying to work with the formula but I think
I may have not stated something correctly. The dates are broken down
by month, not day.(So the month is technically 10/1/2006, 11/1/2006,
etc.. I have a stream of cash flows broken down with each column being
a new month - 60 months out(used Edate). I have already broken down the
cash flows into each month by using a formula somewhat similar to
yours. I want to calculate the total units sold for that given month. I
think the easiest thing would be to divide the cash flow month array
by the total number of Qty column since they correlate. I am willing to
send you the spreadsheet of what I am trying to do if it will make
things clearer. Thank you again for you time - I sincerely appreciate
it.
Dan
Bob Phillips wrote:
[Quoted Text] > I am assuming that begin and end months are actual dates, such as 1/9/2006
> and 31/10/2006, and you want to multiply that daily amount by the number of
> days in the target month, Sep (9) in my example.
>
> =SUMPRODUCT(--(MONTH(E2:E20)<=9),--(MONTH(F2:F20)>=9),G2:G20)*(DATE(YEAR(TOD
> AY()),9+1,1)-DATE(YEAR(TODAY()),9,1))
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> <carlsondaniel[ at ]gmail.com> wrote in message
> news:1159578558.287514.15440[ at ]i42g2000cwa.googlegroups.com...
> > I have an interesting problem. I am trying to break out a quantity
> > number from a list of products from a cash flow. On one row I have a
> > Product name, Quantity, Sales Price per Unit, and Total Sales Price
> > (Qty x SP per U) for my breakdown. Also on this row I have a Beginning
> > Month and an End Month for the range of when the products are sold.
> > This then divides the Products Sales Price evenly throughout the date
> > range specified.
> >
> > Essentially, per row I have one product and all its calculations -
> > and I have a list of over 50 products. They all have different Sales
> > Prices and Dates - and sometimes they are empty.
> >
> > What I am trying to do create a formula that will count the number of
> > units sold in that particular month. I have tried to use some arrays
> > but my knowledge is limited. Any suggestions, questions or feedback
> > would be greatly appreciated! Thank you!!
> >
|