# 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

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..

## Files

Warmest regards
James

• 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 &amp; 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... =)

## Files

Warmest regards
James

## Participate now!

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