Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Create column with descending numbers corresponding to data in another column. (https://www.askmehelpdesk.com/showthread.php?t=270595)

  • Oct 17, 2008, 11:06 PM
    DBuslach
    Create column with descending numbers corresponding to data in another column.
    I created an alphabetized column with names. In another column I put an x next to those names that were heads of households leaving the rest blank. I would like to create a new column that would recognize the x then insert a number in the cell next to the x in descending order (i.e. 1-50).
  • Oct 17, 2008, 11:47 PM
    JBeaucaire
    1 Attachment(s)

    Let's assume the following:

    Column A = X or no X
    Column B = Names
    Column C = the desired numbers

    Here's the formula you put in cell C1:
    =IF(ISBLANK(A1),"",COUNTIF($A$1:$A1,"X"))


    Now, if you've copied/pasted that formula accurately, you can copy that formula down the rest of the C column and the numbers will increment properly. See the pic below.

    Explanation - simply, we look at column A for an X, if we find one, we count all the X's up to that point and show the total.

    When you go to adapt this formula for your own sheet, keep in mind all the references in the COUNTIF function have a $ sign to make them absolute references, except the last part of the second A1. This means as you copy the formula down, the only digit that is allowed to change is that last digit. In the second row, it would appear as $A$1:$A2. This why is works to count all the way up, you've locked the beginning of the range while leaving the end of the range relative.

  • All times are GMT -7. The time now is 09:44 AM.