# Excel - COUNT Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have a formula that counts non blank cells in Cell A2 now i want to create another array formula where in i want to use this count as one of the reference i.e. \$F\$1:\$F\$(value in cell A2). Please advice?
Solved by B. C. in 45 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 08/09/2018 - 09:27
Hi
User 08/09/2018 - 09:27
hi
Excelchat Expert 08/09/2018 - 09:28
Please explain the problem in detail
User 08/09/2018 - 09:28
I am trying to use array formula for sorting.. so that i get the exact unique values in a list
User 08/09/2018 - 09:28
but the problem is my list is dynamic
User 08/09/2018 - 09:29
sometime i have 30 students / 50 students
User 08/09/2018 - 09:29
so now i want to create my array formula in such a way.. that the count of my list is taken into it
User 08/09/2018 - 09:29
=IFERROR(INDEX(\$F\$2:\$F\$10,SMALL(IF(ISNUMBER(SEARCH(\$A\$1,\$F\$2:\$F\$10)),ROW(\$F\$2:\$F\$10)-ROW(\$F\$2)+1),ROWS(\$1:1))),"")
User 08/09/2018 - 09:30
for example.. in this formula.. i am trying to use..
Excelchat Expert 08/09/2018 - 09:30
Would you be able to share the file?
User 08/09/2018 - 09:30
User 08/09/2018 - 09:32
just a sec.. my file was XLSB.. i will convert it into xlsm.. one moment please
Excelchat Expert 08/09/2018 - 09:32
convert it into xlsx
User 08/09/2018 - 09:33
i have done that.. and attached.. so shall i click enter?
User 08/09/2018 - 09:33
oh ok
User 08/09/2018 - 09:34
Now in reality.. by list of column F is dynamic.. it can be 10-50 or even 100 students
User 08/09/2018 - 09:34
my list of*
Excelchat Expert 08/09/2018 - 09:34
okay
Excelchat Expert 08/09/2018 - 09:35
what's the problem here
User 08/09/2018 - 09:35
so in that formula.. I have dictated that F1:F10 right?
Excelchat Expert 08/09/2018 - 09:36
yes
User 08/09/2018 - 09:36
now the reference of F1 is fine.. but F10 cannot be static.. it has to be dynamic.. depending on the list of items in Column F
Excelchat Expert 08/09/2018 - 09:36
okay
User 08/09/2018 - 09:36
so i want that 10 to be picked from count of items
User 08/09/2018 - 09:37
not just putting the value 10 or 100 there
Excelchat Expert 08/09/2018 - 09:37
got it
Excelchat Expert 08/09/2018 - 09:37
ive me sometime to work on it
Excelchat Expert 08/09/2018 - 09:37
*give
User 08/09/2018 - 09:37
User 08/09/2018 - 09:37
This is Manish here
User 08/09/2018 - 09:37
User 08/09/2018 - 09:45
is there anything like.. dynamic reference.. where in the value on cell can be taken as a reference for a range? just guessing.. :)
Excelchat Expert 08/09/2018 - 09:46
I am trying to make the range dynamic only
User 08/09/2018 - 09:46
cool :D thanks
Excelchat Expert 08/09/2018 - 10:04
In the first cell I changed the formula by creating a dynamic range - List1
Excelchat Expert 08/09/2018 - 10:05
And instead of \$F\$2:\$F\$F10, i have used List1 everywhere in the formula
User 08/09/2018 - 10:06
cool ... thanks a lot Sir / Madam for all your help and effort on this
Excelchat Expert 08/09/2018 - 10:06
you are welcome
User 08/09/2018 - 10:07
Sir/Madam.. what is the difference between the 2 lists?
User 08/09/2018 - 10:08
one says Offset
User 08/09/2018 - 10:08
the other is the sheet 1
User 08/09/2018 - 10:10
i think i got that... thanks again sir/madam.. for all your guidance here....

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.