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

1. ## 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. ## 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. ## Re: can I increment with a text ('H7') cell ref? (WinXP, Excel 2007)

=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. ## 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.
eventually work the macro solution into a more permanent application.

thanks again!

Cellmate

5. ## 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. ## 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
•