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

Topic

New Reply

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″,”AF7”}),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

Reply | Quote

Replies

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:

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

Reply | Quote

How about the formula:

=SUMPRODUCT((F7:AF7=0)*ISNUMBER(MATCH(COLUMN(F7:AF7),{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

Reply | Quote

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

Reply | Quote

Or try ……

=SUM(COUNTIF(INDIRECT({“F”;”H”;”J”;”M”;”P”;”AF”}&ROW(7:7)),0))

Regards
bosco

Reply | Quote

thanks, bosco!

That works a treat!

Cellmate

Reply | Quote