I have included a sample file that has column for sales numbers defined by the dynamic range of =Sheet1!$A$2:INDEX(Sheet1!$A$2:A,COUNTA(Sheet1!$A$2:A)) and a second column where I would like to create a formula of the sales ranking that is dynamic and that would update as sales numbers are added to the sales column. The following ranking formula works is I copy it down the column, but I would like it to be dynamic based on the sales.
=COUNTIF($A$2:INDEX(A2:A6,COUNTA(A2:A6)),">"&A2)+1&"/"&COUNT($A$2:$A$6)
Any help would be greatly appreciated.
Nothing attached, when uploading, don't forget to click 'start upload' and wait for the grey check mark. From what I am seeing though, your formula =COUNTIF($A$2:INDEX(A2:A6,COUNTA(A2:A6)),">"&A2)+1&"/"&COUNT($A$2:$A$6) is returning "/" as text instead of as an operator. If you are trying to divide by 4 (the result of your final count in this case, simply remove the ampersands and quotations, and add parenthesis around your countif formula. Your formula would look like this:
=(COUNTIF($A$2:INDEX(A2:A6,COUNTA(A2:A6)),">"&A2)+1)/COUNT($A$2:$A$6)
If that doesn't work try loading your workbook again and we can help you further. 🙂
Thanks Jessica - Attached File
I corrected your named range (You just needed an ending cell address), then whenever hard keying in your sales range cells just use your named range, see attached. Or you could turn your range into a table and use table named ranges. Hope this helps.
