'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Count Distinct Visible Values Based on Priority in Excel
Question details
The user needs to extract a distinct count of visible items from a filtered data export while factoring in a specific priority condition.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing a daily SAP export where rows are filtered and contain duplicate transaction descriptions, requiring a formula to count only unique, visible cells that meet a priority criteria.
- Observed behavior
- Standard counting functions include hidden rows and duplicates. A dynamic formula is needed to isolate and count only unique, visible values meeting the specified conditions.
Ensure your dataset is properly formatted as a table or range without merged cells, and identify the exact column references for your values and priority conditions before applying the formula.
Use Dynamic Array Functions to Count Unique Visible Rows
Combine modern array functions like UNIQUE, FILTER, BYROW, and SUBTOTAL to accurately count distinct visible items meeting your criteria.
This method leverages Excel's dynamic array functions to evaluate visibility and distinctness in memory, without requiring extra helper columns.
Determine the range containing the values you want to count (e.g., L3:L21) and the range containing your priority conditions. Ensure both ranges are exactly the same size.
Use the BYROW and SUBTOTAL functions to create an array that identifies visible rows: BYROW(L3:L21, LAMBDA(a, SUBTOTAL(3,a))). This returns 1 for visible rows and 0 for hidden rows.
Nest this array inside a FILTER function along with your priority condition (e.g., PriorityRange="High"), then wrap it in UNIQUE to extract only distinct values. The multiplication operator (*) is used to combine conditions.
Enclose the entire formula in a COUNTA function: =COUNTA(UNIQUE(FILTER(L3:L21, (BYROW(L3:L21,LAMBDA(a,SUBTOTAL(3,a)))) * (PriorityRange="High")))). Press Enter to get your distinct visible count.
Use a Helper Column for Older Office Versions
If your spreadsheet software does not support dynamic arrays, use a helper column to flag visible rows before filtering.
Count Complex Data Sets Easily with WPS Spreadsheet
WPS Spreadsheet fully supports modern dynamic array functions like UNIQUE, FILTER, and BYROW, making it incredibly easy to handle complex data exports from SAP without relying on clunky workarounds.
- 1. Open your Data: Launch WPS Spreadsheet and open your daily SAP export or project file.
- 2. Apply the Formula: Select a blank cell and input the combination formula: =COUNTA(UNIQUE(FILTER(L3:L21, BYROW(L3:L21,LAMBDA(a,SUBTOTAL(3,a)))))) adjusted for your specific ranges.
- 3. Filter and Update: Apply your data filters directly from the Data tab; WPS Spreadsheet will instantly recalculate and display the accurate distinct visible count.
Frequently Asked Questions
Why does my regular COUNTIF or COUNTIFS include hidden rows?
Standard COUNT functions in spreadsheet software evaluate all cells in the specified range regardless of their visibility. To exclude hidden rows, you must integrate the SUBTOTAL or AGGREGATE function into your logic.
Can I use SUMPRODUCT instead of dynamic arrays for this task?
Yes, in older versions without dynamic arrays, you can combine SUMPRODUCT, SUBTOTAL, OFFSET, and COUNTIF to count distinct visible rows. However, this formula is significantly more complex and can slow down performance on large datasets.
What does the '3' mean in the SUBTOTAL function?
The '3' in the SUBTOTAL function corresponds to the COUNTA function, which counts non-empty cells. If you want to ignore rows hidden manually (not just hidden by filters), use '103' instead.
Why do I get a #NAME? error when using BYROW?
The #NAME? error typically means your version of Excel or WPS Office is older and does not natively support the BYROW or LAMBDA functions. Ensure your software is updated to the latest version that supports dynamic array formulas.
