+ Reply to Thread
Results 1 to 8 of 8

Countifs arrays with multiple criteria and subtotal function

  1. #1
    Registered User
    Join Date
    11-10-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    42

    Countifs arrays with multiple criteria and subtotal function

    All,

    I need some help with COUNTIFS ARRAY formulas that I have in this spreadsheet that are based on multiple criteria. I am trying to figures out how to make them adjust according to visible filtered items from the DATA tab. I have the subtotal functions working properly on all my other calculations (RANGE G15:H19), but I'm having issues with the COUNTIFS type ARRAY formulas (RANGE G20:H21) adjusting and calculating the filtered visible data from the DATA tab when the data is filtered or not.

    I have 2 test columns with 2 different count array formulas. Test2 column is working and adjusting, however, Test1 is not because it does not have a Subtotal Function Element added to it and I would like to know how to add that Subtotal Function to it, so that I can test both arrays. As a result, I'm questioning the variances I'm getting of. I'm not sure which one is correct. I attached the XL file with added notes to the DB tab to help you troubleshoot my problem.

    I really appreciate your help so much guys!
    Attached Files Attached Files

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Countifs arrays with multiple criteria and subtotal function

    I think you have almost answered your own question. The COUNT(IF array formula and the COUNTIFS version will count all rows, visible or not. The SUMPRODUCT/SUBTOTAL version is the best way to count or sum with conditions whilst ignoring filtered out rows (unless you want to use a helper column)
    Audere est facere

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Countifs arrays with multiple criteria and subtotal function

    Quote Originally Posted by daddylonglegs View Post
    The SUMPRODUCT/SUBTOTAL version is the best way to count or sum with conditions whilst ignoring filtered out rows (unless you want to use a helper column)
    I was thinking that the new AGGREGATE function (Excel 2010) would be a good alternative to the standard SUMPRODUCT/SUBTOTAL but I'll be darned if I can get it to work.

    In this case the help file entry on the AGGREGATE function is downright pitiful.
    Last edited by Tony Valko; 11-11-2013 at 09:21 PM.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    11-10-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Countifs arrays with multiple criteria and subtotal function

    Hey guys. Thank you so much for the quick response. OK. So, if I went with the the SUMPRODUCT/SUBTOTAL option, when I evaluate both array formulas the 1st one below is counting 164 items while the 2nd one is counting 141. Why is that? Which one is counting accurately when there is no filter on? Because I assume, both array formulas should at least have the same count result when the filter is not on for both of them, right? I realize one of them has the subtotal function and the other doesn't, but either way they should have the same result from a non filtered scope scenario.

    I just want to make sure that if I go with Option 2. below that I'm not missing out on accuracy. I want to be able to explain why one is correct and the other isn't.

    1. {(COUNT(IF(RngVal1>=$G$19,IF(RngVal1<=$G$18,1,0))))} = 164

    2. SUMPRODUCT(SUBTOTAL(3,OFFSET(RngVal1,ROW(RngVal1)-MIN(ROW(RngVal1)),,1)),--(RngVal1>=$H$19),--(RngVal1<=$H$18)) = 141
    Last edited by 5150; 11-11-2013 at 09:14 PM.

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Countifs arrays with multiple criteria and subtotal function

    141 is correct. In the COUNT formula COUNT counts any numbers, including zero so because the formula returns 1 for TRUE and zero for FALSE those are both numbers so it's counting all rows, if you change to this version

    =COUNT(IF(RngVal1>=$G$19,IF(RngVal1<=$G$18,1)))

    confirmed with CTRL+SHIFT+ENTER

    then you'll get 141 as you will with the COUNTIFS function

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Countifs arrays with multiple criteria and subtotal function

    I didn't look at your file...

    In the COUNT(IF array formula remove the 0 at the end:

    =COUNT(IF(RngVal1>=$G$19,IF(RngVal1<=$G$18,1)))

    That will/should return the correct result when no filter is applied. It will not work correctly when the filter is applied. It does not exclude any hidden rows when the filter is applied.

    We need to use the SUMPRODUCT/SUBTOTAL version to exclude hidden rows when the filter is applied.

  7. #7
    Registered User
    Join Date
    11-10-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Countifs arrays with multiple criteria and subtotal function

    Sweeet. Right on. Thank you so much! I really appreciate the thorough explanation to enhance my understanding on these formulas. Take care.

  8. #8
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,162

    Re: Countifs arrays with multiple criteria and subtotal function

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 08-05-2013, 01:52 PM
  2. Subtotal for countifs function
    By guyzk1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-02-2013, 04:39 PM
  3. Replies: 4
    Last Post: 03-30-2013, 08:36 AM
  4. Multiple criteria arrays in countifs function resulting in incorrect answer
    By laurenmoney in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-28-2013, 12:57 PM
  5. [SOLVED] Countifs function with multiple criteria in same column
    By sam99 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-28-2012, 09:10 AM

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