• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Show months for dates.

exceladdict

New Member
hi,


i want to know the formula for displaying the months with year from the dates.I had a worksheet with columns:-

A: Products A,B,C & So on....

B: Date Sold

C: Quantity

D: Amount.


Criteria:I want to show the summary of the products sold monthly,like,

For product A; All the products A which are sold between 15th of this month to 14th of next month will be summed up for a single month.


Please help me..
 
For Product A

Code:
For Qty Sold: =sumproduct(+1*(DateRange>=Start Date)*(Date range<=End Date)*(Product Range=Product A),(Quantity Range))


[code]For $ Sold: =sumproduct(+1*(DateRange>=Start Date)*(Date range<=End Date)*(Product Range=Product A),(Quantity Range)*(Amount Range))


For All Products
For Qty Sold: =sumproduct(+1*(DateRange>=Start Date)*(Date range<=End Date),(Quantity Range))


[/code]For $ Sold: =sumproduct(+1*(DateRange>=Start Date)*(Date range<=End Date),(Quantity Range)*(Amount Range))
 
Thanks HUI, for the reply.but sorry,i fear i had not explained my problem clearly.


Can i send the excel sheet to you so that the problem would be clear enough for understanding.


Please tell me how to send the attachment.
 
E3: =B3-14

Copy down

Then apply a custom number format

Select cells

Ctrl 1

Custom Number Format mmm yy
 
Thanks for the reply hui.My doubt is how would this logic work for all the products as there are different dates for different products.please explain.
 
Use a lookup table and subtract numbers based on each product type

eg: =B3-vlookup(product, table, Col 2)
 
You need a table of Product and Offset days

eg:

Product Offset

Product 1 offset days 1

Product 2 offset days 2

Product 3 offset days 3

Product x offset days x


Assuming the above table is in AA1:AB5


Then your equation becomes

=B3-vlookup(A3, $AA$2:$AA$5, 2, False)


Copy down
 
Back
Top