Finding Minimum but if same number repeats in the range, then find
Can someone help me? I am trying to find a way to make a list of numbers from
a range. The numbers can repeat many time in the range but I want to list each different number once only. I tried to use MINIF function but if the range has only one number, then this function can not be used. For example: I have a range D1:Q25, I want to make a summary of numbers used in this range. Column A will have the each different number used in this range and Column B will show the number of times each number is used. If my Colum A has the right number, then I can use COUNTIF fuction for Coumn B. I tried for "A1" =MIN(D1:Q25) and for "A2" =MIN(IF(D1:Q25A1,D1:Q25,"")) and for "A3" =MIN(IF(D1:Q25A2,D1:Q25,"")) but if A2 is blank, then I get same number as A1. |
Finding Minimum but if same number repeats in the range, then find
Enter this formula in A1:
=MIN(D1:Q25) Enter this array formula** in A2 and copy down until you get blanks: =IF(OR(A1="",A1=MAX(D$1:Q$25)),"",MIN(IF(D$1:Q$25 A1,D$1:Q$25))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Then your COUNTIF in B1: =COUNTIF(D$1:Q$25,A1) Copied down as needed. -- Biff Microsoft Excel MVP "MIK" wrote in message ... Can someone help me? I am trying to find a way to make a list of numbers from a range. The numbers can repeat many time in the range but I want to list each different number once only. I tried to use MINIF function but if the range has only one number, then this function can not be used. For example: I have a range D1:Q25, I want to make a summary of numbers used in this range. Column A will have the each different number used in this range and Column B will show the number of times each number is used. If my Colum A has the right number, then I can use COUNTIF fuction for Coumn B. I tried for "A1" =MIN(D1:Q25) and for "A2" =MIN(IF(D1:Q25A1,D1:Q25,"")) and for "A3" =MIN(IF(D1:Q25A2,D1:Q25,"")) but if A2 is blank, then I get same number as A1. |
All times are GMT +1. The time now is 03:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com