Formula for unique records that satisfy a row condition
I want to be able to provide a count of distinct values of one column ("ID", in column B below) where a value in the respective row (eg "Value" in Column C below) meets a criteria (eg <>0) below. As you can see, I can easily compute the list of unique ID's (cell B8, with formula in C8) but can't fathom how to pair this with the discovery of non-zero values.
I've formulated an answer to sort of a similar question here. Particulary handy if you want to extend with multiple criteria.
In your case it would look a bit more symplistic cause you have numeric values (ID's)
Entered through CtrlShiftEnter
Excel formula: Unique values with criteria, To extract a list of unique values from a set of data, while applying one or more logical criteria, you can use the UNIQUE function together with the FILTER� This article shows you how to do it using an array formula: Filter unique distinct records with a condition The difference with today's blog post is that you also can add more filters to your Excel Table, making the Excel Table a lot more useful and powerful.
You can use the double unary operator (
--) to revert a true/false to 0 or 1 then use that in the sumproduct like so:
Excel formula: Count unique text values with criteria, Excel formula: Count unique text values with criteria. Generic Next, the ROW function is used to build a sequential list of numbers for each value in the data:. Note: In the above formula: B2:B17 is the column range contains the unique values that you want to extract from, A2:A17 is the column contains the criterion you based on, D2 indicates the criterion that you want to list the unique values based on, and E1 is the cell above your entered formula.
Here's another (non array) option
Or, if the data is a Table as your image suggests
Count unique distinct values that meet multiple criteria, But I can seem to use that formula with thousands of rows. You have 26 rows, imagine you have 10000. Do you know if there is a limit to that� Type this formula into Cell E2, and press Ctrl + Shift + Enter keys to change it as Array formula. The drag the AutoFill handle until you get the #N/A value. You would notice that the unique product name list is extracted in column E. Note: the range B2:B6 is a range that contains the unique product name list that you want to extract. A2:A6 is
How to extract unique values based on criteria in Excel?, Extract unique values based on criteria with array formula Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns. Now we add a condition. Because this formula regards empty cells as one unique record, the condition might be, that cells mustn’t be empty. The condition can be entered instead of the 1 in the beginning of the SUMPRODUCT formula like in the image on the right-hand side.
How to count unique values based on another column in Excel?, It may be common for us to count unique values in only one column, but, in this To solve this problem, the following formula can help you, please do as follows: count the unique values, D2 contains the criteria that you count unique based on. without losing Data; Split Cells Content; Combine Duplicate Rows/Columns. To return unique values from the horizontal range A1:E1, set the by_col argument to TRUE or 1: = UNIQUE( A1:E1,1) // extract unique from horizontal array. The UNIQUE function has an optional argument called exactly_once that controls how the function deals with repeating values. By default, exactly_once is FALSE.
This generic formula can be used to count unique values with Or logic. It means, it will count if any of the condition is true. So yeah guys, this how you count unique values in a range on multiple conditions. This is a little bit complex but it is fast. Once you start using it, you will get how it works.
- Would that be Frequency for Interval? FWIW I much prefer your frequency method to the countif method.
- @TomSharpe, i'm sorry, it should be frequency. Interval is the dutch equivalent.
- Nod to @Glitch 's answer, only added it as some might consider it "simpler"