Thread: COUNT on three criteria, but using no arrays or pivot tables

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?

can you upload an workbook with some example data?

Hi,

Take a look here, this might help?

http://www.grbps.com/Excel3.pdf

Is this a good start?

Book2(1).xls

Don't know how to make data 6 months before without TODAY()

Hi,

A little more reading?

http://www.decisionmodels.com/optspeedj.htm

I think OC had exactly the right suggestion.

       -----A------ ------B------- ----C----- ----D----- E ----------------F----------------
   1   Entry Number Date Submitted Source     Type                                          
   2                >39769         Purchasing Complaint  2 E2: =DCOUNT($A$11:$D$18, , A1:D2)
   3                                                       B2: =">" & EDATE(TODAY(), -6)    
   4   Entry Number Date Submitted Source     Type                                          
   5                >39585         Shipping   Complaint  2                                  
   6                                                                                        
   7   Entry Number Date Submitted Source     Type                                          
   8                >39585         Shipping   Compliment 1                                  
   9                                                                                        
  10                                                                                        
  11   Entry Number Date Submitted Source     Type                                          
  12   Entry001     05/01/2009     Purchasing Complaint                                     
  13   Entry002     05/02/2009     Accounting Compliment                                    
  14   Entry003     05/03/2009     Purchasing Complaint                                     
  15   Entry004     06/04/2008     Shipping   Complaint                                     
  16   Entry005     05/02/2009     Shipping   Compliment                                    
  17   Entry006     03/02/2008     Purchasing Complaint                                     
  18   Entry007     05/02/2009     Shipping   Complaint