A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Hi,
Enter this formula in cell B17
=COUNTA(UNIQUE(FILTER(A2:A11,BYROW(B2:B11,LAMBDA(a,SUBTOTAL(103,a))))))
Hope this helps.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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!
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
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.
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
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))
From the screenshot i shared, one can very clearly see that it does.
Did not work for me. Thanks