I have a table which, when populated, gets huge. The example I am showing here is greatly simplified.
I need to be able to provide a summary report that shows a count of entries (rows) that meet either TWO or THREE criteria, and one of those criteria is date-based.
For the date-based option, I need two possibilities: 6 months ago, and 12 months ago.
In the attached example, then, I would need to see:
- How many entries were for Purchasing and were Complaints in the past 6 months? (answer should = 2)
- How many entries were for Shipping and were Complaints in the past 12 months (answer should = 2)
- How many entries were for Shipping and were Compliments in the past 12 months (answer should = 1)
And so forth.
Here's the rub. All of this can be done with array formulas, and an EDATE cell that calculates today's date -6 months, and one -12 months. The problem there is that there would be so many array formulas (I need to do a lot of permutations), plus utilizing the volatile TODAY(), that the sheet slows down DRAMATICALLY in calculation time.
Secondly, this is for a user that is not Excel savvy. So the solution not only should not slow the calculations down, or require them to set Excel to manual calculation, but it should also NOT use Pivot Tables. (Simple users are confused by pivot tables, sorry to say... and they are a heck of a thing to format nicely for the boss to look at.)
And, of course, we can't use VBA.... that will also confuse the client.
So I know the best answer lies in database functions. I just cannot figure it out.
Ideas?
Bookmarks