Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Dear loungers,

    In Excel 2007 you can use COUNTIF with multiple criteria.. However what should I do in XP to acheive the same.

    For example I have workbook with a Summary sheet that is based on Change Request data in another sheet. The summary should show Change Requests by Status and Priority - each Change Request have both a status and a priority. This means that in the summary table the left hand column is the Priorities and the top row is the Status value, the body of the sheet contains intersections between these so, for example, I want to count all Change Requests where priority is High and status is Raised. I have attached a simplified version of the sheet but without any formula on the summary sheet. How can I do this?

    thank you............................ liz
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Liz,

    Attached is your workbook with an added sheet for criteria ranges.
    You need to use the DCount function as shown in the sheet. This will require creating a range for each pair of criteria {Status/Agreed Priority) and naming that range. Note the 2nd parameter in the DCount function is blank but the 2 commas must remain! Also take note of how the values are entered in the criteria ="=raised".

    I had a problem getting this to work and I finally figured out that in your Ref Data sheet you had a space behind every one of the status entries you use for your dropdown. I delete these then I had to re-enter all the status entries to make them valid. This is the kind of problem which can be very hard to track down. I knew I had the DCount function right and couldn't figure out why I wasn't getting the correct counts.

    Good Luck.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello - A Pivot Table may do what is needed. See attached sample on Pivot Summary tab

    No VBA/Macros are needed.

    If detailed trail of any number is needed - just double click the number. For Example: Double click the 13 in medium/raised and you will see the 13 records involved.

    Tim
    Attached Files Attached Files

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I'd use SUMPRODUCT in Excel Prior to 2007.
    After that COUNTIFS is faster because it is better optimized for the task

    I extended the ranges in your example, the actual range down you use depends on how much data there is likely to be.
    Ideally you would use named ranges.

    =SUMPRODUCT(('Change Request Examples'!$E$3:$E$1319=Summary!$A4)*('Change Request Examples'!$B$3:$B$1319=Summary!B$3)*1)

    [attachment=89348:Example Sumproduct.xls]
    Attached Files Attached Files
    Andrew

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Andrew,

    Nice
    I've never used SumProduct before but thanks to your example I have a grasp on it's use.
    I've attached a worksheet using your SumProduct and adding Dynamic Range Names to shorten the formula.
    I've also pasted a list of all the defined names and definitions into the Ref Data sheet for reference.

    Liz please take notice of the Mixed references used by Andrew and continued in my example which allow you to enter the formula once then copy it down and across.

    I just love these forums
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Andrew,

    Just FYI, there is no need for the *1 at the end of yours since you are already coercing the True/False values with multiplication.

    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I know, but you are correct of course, it is redundant in this example.
    However, as an example if they only used a single test it would be needed,
    so it is a nice safety net to build in.

    Also it helps when people look back at it later to indicate that they are using it to count. Well it does for me anyway.
    Andrew

  8. #8
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    You guys are wonderful!!! And I too love the lounge it's the best and most actively contributed to that I know AND by people that really know not by pseudo-experts.

    Thank you for all the tips I am now busy making it work - and thank you "retired greek" (I thuoght that was impossible!) for the debugging I too would have been foxed.

    long live the lounge!......................... liz

Posting Permissions

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