Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jul 2003
    Location
    Brisbane, California, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    can I increment with a text ('H7') cell ref? (WinXP, Excel 2007)

    Greetings!

    I have a loverly formula (does what I want) that uses text-based cell references to operate on
    noncontiguous cells. I want to "do the usual thing" and enter the formula in the top cell, copy it
    down and have all the cell refs increment as the row changes.

    The formula is-- =SUM(COUNTIF(INDIRECT({"F7","H7","J7","M7","P7","A F7"}),0))

    Is this just a pipe-dream, or can it be done some way?
    This month the data set is approx 50 rows: no telling about next month...

    TIA,

    Cellmate

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: can I increment with a text ('H7') cell ref? (WinXP, Excel 2007)

    Someone will probably come up with a clever formula solution, but here is a macro you can use. It assumes that you want the formulas in column AZ, change as needed:
    <code>
    Sub MakeFormulas()
    Dim r As Long
    Dim strFormula As String
    For r = 1 To Cells(Rows.Count, "f").End(xlUp).Row
    strFormula = "=SUM(COUNTIF(INDIRECT({""F" & r & """,""H" & r & _
    """,""J" & r & """,""M" & r & """,""P" & r & """,""AF" & r & """}),0))"
    Cells(r, "AZ").Formula = strFormula
    Next r
    End Sub</code>

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: can I increment with a text ('H7') cell ref? (WinXP, Excel 2007)

    How about the formula:

    =SUMPRODUCT((F7:AF7=0)*ISNUMBER(MATCH(COLUMN(F7:AF 7),{6,8,10,13,16,32},0)))

    [In case it is not obvious, the 6,8,10,13,16,and 32 are the column numbers to include in the count: F,H,J,M,P, and AF, respectively]

    Steve

  4. #4
    Lounger
    Join Date
    Jul 2003
    Location
    Brisbane, California, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: can I increment with a text ('H7') cell ref? (WinXP, Excel 2007)

    Has anyone mentioned, lately, that you guys are AMAZING!

    Hans, Steve, Thanks so much.

    It will be a little later today before I'll have a chance to try these.
    I expect I'll start with the formula for immediate purposes and
    eventually work the macro solution into a more permanent application.

    thanks again!

    Cellmate

  5. #5
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: can I increment with a text ('H7') cell ref?

    Or try

    =SUM(COUNTIF(INDIRECT({"F";"H";"J";"M";"P";"AF"}&R OW(7:7)),0))

    Regards
    bosco

  6. #6
    Lounger
    Join Date
    Jul 2003
    Location
    Brisbane, California, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: can I increment with a text ('H7') cell ref?

    thanks, bosco!

    That works a treat!

    Cellmate

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •