Count Unique values with a condition

by

Assuming no more than 100 rows try this “array formula” to count the different names in A2:A100 where there is a 1 in the same row in B2:B100:

=SUM(IF(FREQUENCY(IF(B2:B100=1,IF(A2:A100<>"",MATCH(A2:A100,A2:A100,0))),ROW(A2:A100)-ROW(A2)+1),1))

confirmed with CTRL+SHIFT+ENTER

Note that I say different not unique as the two are not the same

More Related Contents:

  1. Calculation is wrong, Rounding wrong
  2. Simple Pivot Table to Count Unique Values
  3. Concatenate column headers if value in rows below is non-blank
  4. Optimizing Excel formulas – SUMPRODUCT vs SUMIFS/COUNTIFS
  5. Convert an Array Formula’s Text Results into a Usable Format
  6. Want to concatenate multiple row data in in cell along with its values if present. Emp Id should not come if no data available infromt of it
  7. How to convert to percentage [closed]
  8. Excel Number Separation [closed]
  9. Return empty cell from formula in Excel
  10. Excel: last character/string match in a string
  11. Referencing value in a closed Excel workbook using INDIRECT?
  12. How can I perform a reverse string search in Excel without using VBA?
  13. Excel UNIQUE Across Columns
  14. Filling any empty cells with the value above
  15. How to create a string or formula containing double quotes in Excel?
  16. Remove leading or trailing spaces in an entire column of data
  17. Excel: Formulas for converting data among column / row / matrix
  18. Count unique values in Excel
  19. Three Dimensional Lookup Using INDEX/MATCH
  20. How to list all possible combinations of the values in three columns in excel?
  21. Excel – extracting top 5 values
  22. How to split texts from dynamic range?
  23. Excel: Search for a list of strings within a particular string using array formulas?
  24. excel vlookup with multiple results
  25. How to roundup a number to the closest ten?
  26. Date-Formatting in TEXT formula independent from used language in Excel
  27. Can’t pass a range to a lambda using Let and Makearray functions
  28. Check whether a cell contains a substring
  29. Unique Rank value for a subgroup within a group
  30. Get content of a cell given the row and column numbers

Leave a Comment