Count By Criteria & Date

  • dear all,


    i have a question here.


    Attachment:
    Exmaple.xls ( Unsorted & Sorted worksheets )


    Explanation of worksheets:
    1. Unsorted worksheet - contains the raw data, which the following heading:
    a. Department - (example: Marketing, Finance, Acccounts, Warehouse etc etc)
    b. MonthYear of Purchase - MMYYYY (example: 012007)
    c. Purchase Category - (example: miscelleneous, food, repair, welfare etc)
    d. Purchase amount - (example: $123.45)


    2. Sorted worksheet - contains the final outcome of my sorting


    Task:
    I need to provide a table, where it would give me an idea of how many different purchase category did the department commit in a particular month and year.


    what i have tried:
    I used pivot tables, and the count. But i realise it must be distinct values, meaning, each pruchase must be different in order to use the count function meaningfully in pivot tables.


    Because i would have an instance where in the month of 022007, marketing dept onli purchase 1 category. How can i do it?


    In SQL... it sounds like:


    Select distinct ( Product Category )
    From Unsorted
    Where Month = Month
    And Dept = Dept.


    Can someone help?


    Thanks..

  • Re: Count The Number Of Different Purchase Category By Dept And Monthyear


    Use proper dates and smply format them to show only the month & year. There is no reason then a Pivot Table wont do the task.

  • Re: Count By Criteria & Date


    dear Dave,


    I have created an pivot table in the sorted worksheet and repost an attached, kindly refer.


    I agree with you with the date settings. I have changed it to year instead.


    Now the thing is... I have created a pivot table. The pivot table only counts the number of entries but does not gives me a count distinct value only.


    If you compare the result given by pivot table with what the ideal structure,


    in the year 2005, Marketing dept made 6 purchase entries (2 x entertainment, 2 x food, 2 x repair = 6), that's correct.


    But what i am looking at is the number of different categories they have commited the purchase categories, which is only 3 ( entertainment, food, repair ).


    Hope this gives you a better picture of my problem.


    Thanks alot... =)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!