Count By Criteria & Date

  • dear all,

    i have a question here.

    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

    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?


  • 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!