Multiple Criteria and Partial Date Matching

I have 2 columns; one for product and one for date. The product column (text) includes 6 different products, and the date column (mm/dd/yyyy) lists when they were sold.(NOTE: there is a 3rd column which includes exact time product was sold, but that information is not part of this question.) So I have multiple occurrences where the product in column A matches the date in column B.

I'm looking to truncate this information by month and by product into a nice table for visualization. I need to build an equation that counts how many of each product was sold in each month.

My master list is enormous, so anything to keep this automated would be appreciated. Thanks in advance.

Probably a Pivot Table would suit your purpose here.... it can be grouped by month or day or year....

Check here for some intro:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

http://datapigtechnologies.com/ExcelMain.htm

If you need more help afterwards, post a sample workbook.

Does the attach work for you?

If you add a helper column to extract the month/year from column B with formula in D2: =DATE(YEAR(B2),MONTH(B2),1) copied down.

Then you can create a table of products vs. month/year and use COUNTIFS.

So if table is in J1:O7, then in K2:

=COUNTIFS($A:$A,$J2,$D:$D,K$1)

copied across the matrix.

Note: You can also do it without helper columns.. but then you would need SUMPRODUCT() instead of COUNTIFS() and would be much less efficient.

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save