Multiple Criteria and Partial Date Matching

**GreatLakesJK** · 07-08-2010, 09:25 AM

Originally Posted by NBVC

Does the attach work for you?

Yes and no. The data looks great, but for this scenario I would like not to use a pivot table. The way the data is being imported, I think I would be best off without a table.

Is there a viable solution without using a pivot table?

**NBVC** · 07-08-2010, 09:38 AM

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.