Share via

How do I count "Distinct" text items using a "Subtotal" function to exclude count of filtered or hidden line items.

Anonymous
2023-09-22T01:00:58+00:00

Question: How do I count "Distinct" text items using a "Subtotal" function to exclude count of filtered or hidden line items.

I wanted to count the "Distinct" Product Groups in the list of 21 items (product groups counted once for repeating line items).

I came up with 10 "Distinct" Product Groups using the following formula:

{=SUM(IF(ISTEXT(C11:C31),1/COUNTIF(C11:C31, C11:C31),""))}

(The formula requires the CTRL + SHIFT + ENTER keys to be selected when done editing the formula for it to work and to add the "{" and "}" curly brackets at the start and end)

This is great to count the product groups used in the list when a much larger list is used or a report of specific groups that are exported. However, I am having trouble converting it to a "Subtotal" version that would only show "Visible" items that are "Not" filtered or hidden. Any ideas? Would this be a substitution for the SUM and COUNTIF functions with Subtotal versions? I am using Excel version 2019 2308 Build an tying to do it in one formula without helper cells, if possible. Thanks!

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

7 answers

Sort by: Most helpful
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2023-09-23T00:05:06+00:00

    Hi,

    Enter this formula in cell B17

    =COUNTA(UNIQUE(FILTER(A2:A11,BYROW(B2:B11,LAMBDA(a,SUBTOTAL(103,a))))))

    Hope this helps.

    10+ people found this answer helpful.
    0 comments No comments
  2. Kevin Jones 7,265 Reputation points Volunteer Moderator
    2023-09-22T02:00:03+00:00

    You will need a helper column that indicates whether the row is hidden or visible. Here is a tutorial by Bill Jelen:

    https://www.youtube.com/watch?v=mE1tQZbiHEA

    Kevin

    4 people found this answer helpful.
    0 comments No comments
  3. Anonymous
    2023-09-30T02:04:49+00:00

    Hi,

    Here is another approach:

    =SUM(SUBTOTAL(3,OFFSET(A$1,ROW($7:$90)-1,))*($A$7:$A$90=$C4))

    This data is filtered on the Code column and only a small portion of the range has data - rows 7:11. This formula is tricky. The OFFSET is relative to A1 although that is not necessary its simpler to follow. 3 is the COUNTA function.

    Using SUM is not an issue since the SUBTOTAL function is checking only one row at a time, thus it returns 1 or 0.

    If you are not going to copy the formula, then you can simplify it to:

    =SUM(SUBTOTAL(3,OFFSET(A1,ROW(7:90)-1,))*(A7:A90=C4))

    0 comments No comments
  4. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2023-09-29T23:18:07+00:00

    From the screenshot i shared, one can very clearly see that it does.

    0 comments No comments
  5. Anonymous
    2023-09-29T23:10:58+00:00

    Did not work for me. Thanks

    0 comments No comments