Using =IF(B10<>"",COUNTA($B$10:B10),"") to number rows in columns other than A

Discussion in 'Other Software' started by Burbank Frank, May 20, 2016.

  1. Burbank Frank

    Burbank Frank

    Joined:
    May 20, 2016
    Likes Received:
    0
    I tried out this formula, =IF(B10<>"",COUNTA($B$10:B10),""), to number the rows in a column that have data in adjacent columns, but leave a row un-numbered and uncounted if the adjacent cell was blank. It worked great in Column A.

    However, I tried using the formula in column D and it didn't work so well. Instead of numbering in sequence it was counting by 3's. 3, 6, 9, 12 etc. It still skipped the rows with no data in the adjacent cells but the numbering was wrong.

    I tried making some changes no no avail.

    This is the formula in D10: =IF(E10<>"",COUNTA($B$10:E10),"")

    Any ideas on how to make this formula work in Column D?

    Frank
     
    Burbank Frank, May 20, 2016
    #1
    1. Advertisements

  2. Burbank Frank

    alow Excel Formulator

    Joined:
    Mar 20, 2012
    Likes Received:
    4
    Location:
    US
    Frank,

    I think your problem is with your formula having a static reference for the first part of the array in the COUNTA (The dollar signs). Since you have the reference in the original location as $B$10:B10, when you copied it over, it kept the reference so your new location is counting across columns B through E. You can fix this 1 of 2 ways. You can modify only the formula in column D to read =IF(E10<>"",COUNTA($E$10:E10)"").

    Alternatively, you could change your original formula and recopy it. The dollar signs will keep the reference they are in front of static, so if you changed your original formula to =IF(B10<>"",COUNTA(B$10:B10),"") and copied it, the column label would change relatively.

    The final recommendation I'm going to give is a new formula that I think gives you the same result but with easier formulas.

    =IF(ISBLANK(B10),ROW(),"")

    This will give a true/false value based on whether or not the targeted cell is blank, and then return the current row number. Let me know if you need any further assistance!
     
    alow, May 24, 2016
    #2
    Becky likes this.
    1. Advertisements

  3. Burbank Frank

    Burbank Frank

    Joined:
    May 20, 2016
    Likes Received:
    0

    Alow, I figured out the Absolute Cell Reference problem myself and adjusted the original formula to correct for it's location in the spreadsheet. I haven't tried your alternate formula yet but thanks for offering it.

    Addendum: I just tried your alternate formula in my test spreadsheet. Unfortunately the only rows that got numbered were the ones with adjacent empty spaces (exactly opposite of what I was going for).

    Frank
     
    Burbank Frank, May 24, 2016
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.