Unique Rank value for a subgroup within a group

by

The good news is that you can throw away the SUMPRODUCT function and replace it with a pair of COUNTIFS functions. The COUNTIFS can use full column references without detriment and is vastly more efficient than the SUMPRODUCT even with the SUMPRODUCT cell ranges limited to the extents of the data.

In N2 as a standard function,

=COUNTIFS(B:B, B2,M:M, "<"&M2)+COUNTIFS(B$2:B2, B2, M$2:M2, M2)

Fill down as necessary.

      rank_unique_subgroups_sample_data

  Filtered Results

     rank_unique_subgroups_group_A filtered results  rank_unique_subgroups_group_B_ filtered results rank_unique_subgroups_group_C filtered results

More Related Contents:

  1. Calculation is wrong, Rounding wrong
  2. Excel formula checklist in employees
  3. Sequence of numbers based on start and end value of 2 cells [closed]
  4. Excel VBA – How to add dynamic array formula
  5. TEXTJOIN for xl2010/xl2013 with criteria
  6. Difference between date and time w/out work week Excel
  7. Ignore Duplicates and Create New List of Unique Values in Excel
  8. Excel Formula for Inventory
  9. Count Unique values with a condition
  10. Using UNIQUE with non-adjecent columns on different sheets
  11. Count unique values in Excel
  12. Concatenate column headers if value in rows below is non-blank
  13. If two cells match, return value from third
  14. What does — do in Excel formulas?
  15. How do I apply a SumIf Formula in a VBA Sub? Getting Error 1004
  16. Using the value in a cell as a cell reference in a formula?
  17. How to list all possible combinations of the values in three columns in excel?
  18. Excel – extracting top 5 values
  19. CountIf With Filtered Data
  20. How to count cells in a range with a value less than another cell in excel?
  21. Extract the last substring from a cell
  22. How to extract text within a string of text
  23. Excel: How to gather unique values in one column that are associated with duplicates in another column?
  24. excel vlookup with multiple results
  25. How to roundup a number to the closest ten?
  26. Convert an Array Formula’s Text Results into a Usable Format
  27. Date-Formatting in TEXT formula independent from used language in Excel
  28. Can’t pass a range to a lambda using Let and Makearray functions
  29. Check whether a cell contains a substring
  30. Get content of a cell given the row and column numbers

Leave a Comment