+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Registered User
    Join Date
    12-06-2003
    Posts
    21

    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?
    Last edited by paris3; 05-19-2009 at 12:18 PM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

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

    can you upload an workbook with some example data?

  3. #3
    Registered User
    Join Date
    12-06-2003
    Posts
    21

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

    Yes, sorry, having trouble uploading... gimme a sec.... GRRR

  4. #4
    Registered User
    Join Date
    12-06-2003
    Posts
    21

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

    OK here is the sample. (Tab controls in Firefox were preventing popups from working.)
    Attached Files Attached Files

  5. #5
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097

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

    Hi,

    Take a look here, this might help?

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


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

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

    Is this a good start?

    Book2(1).xls

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

  7. #7
    Registered User
    Join Date
    12-06-2003
    Posts
    21

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

    Quote Originally Posted by oldchippy View Post
    Hi,

    Take a look here, this might help?

    http://www.grbps.com/Excel3.pdf
    Nope, I found that link a while ago and studied that one for days before posting here (I always make posting here my last resort, as I hate tying up other people's time... but am grateful when they do help.)

    Didn't help me, but thanks though.

  8. #8
    Registered User
    Join Date
    12-06-2003
    Posts
    21

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

    Quote Originally Posted by zbor View Post
    Is this a good start?

    Attachment 43922

    Don't know how to make data 6 months before without TODAY()
    That's not a problem. A single use of TODAY() won't chew up too much processor time, it's just when you combine it with 20 - 30 array formulas, it makes things exponentially worse.

    Your solution using SUMPRODUCT is *NOT* an array formula???? Hmm.... I just assumed that wasn't even possible!

    I am actually shocked that it's possible to do it that way... I have to study it. About to jump in my car, so will comment back in a while. This may be a really, really good solution!

  9. #9
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097

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

    Hi,

    A little more reading?

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

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

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

    I think OC had exactly the right suggestion.
    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  11. #11
    Registered User
    Join Date
    12-06-2003
    Posts
    21

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

    OK, here's an update.

    The solution by zbor to use SUMPRODUCT, worked, but it cranked calculation times to unbearable levels. The result was similar to using arrays, so I couldn't use that option. Thanks, though.

    The info by OC and the sample provided by SHG helped. With my (minor) attention deficit disorder, it was hard for me to interpret the generic samples given on that web link to my specific case. The sample code that SHG worked up helped me interpret it for my situation.

    So far everything is working. When it's all done, I will post a screenshot of this thing, it's massive, but it calculates in a second... not 10 - 15 seconds!

    A few wrinkles still being worked, but I will report back when I have it done, or if I have any additional questions.

    Thanks so far, guys!

  12. #12
    Registered User
    Join Date
    12-06-2003
    Posts
    21

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

    OK final update... everything is working. See the image for the large set of database criteria tables I had to set up (image is obscured a bit to hide sensitive info, even though it's all dummy data anyway.)

    So the database functions worked, just had to set up a lot of criteria tables. But the sheet calculates on the fly at lightning speed, no 15 second recalc times. Exactly what I wanted!

    Thanks to all who helped. Will mark the thread as solved.
    Attached Images Attached Images

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1