Solved: countif question

[vzachin]

hi,

is there an easy way to count additional columns?

i have a formula in column A:
Range("A3").Formula = "=COUNTIF(A5:A65536,""<>"")"

What I want is to count all the entries beginning in column A5 and then column E5 and every 4th column thereafter. (A5,E5,I5,M5..etc until the last column IS5).
If there are no entries in a column, then that would be the last column with any entry.
There will always be an entry in column A.


thanks
zach

[rbrhodes]

Hi zach,

Looks like you're using VBA already. Here's a UDF and a Sub that both use Countif. The UDf goes in a cell as :

=Cols()

The code is code.

 
Option Explicit
 
Function Cols()
 
Dim x As Long
Dim Totl As Long
 
'Col A
Totl = Application.WorksheetFunction.CountIf(Range(Cells(5, 1).Address, Cells(65536, 1).Address), "<>")
'Col E
Totl = Totl + Application.WorksheetFunction.CountIf(Range(Cells(5, 5).Address, Cells(65536, 5).Address), "<>")
'remainder of Cols: Col # - 9 / Mod 4 = 0
For x = 9 To 253 Step 4
' add Col# countif to total
Totl = Totl + Application.WorksheetFunction.CountIf(Range(Cells(5, x).Address, Cells(65536, x).Address), "<>")
Next x
 
'return as function
Cols = Totl
 
End Function
 
Sub CountCols()
 
Dim x As Long
Dim Totl As Long
 
'Col A
Totl = Application.WorksheetFunction.CountIf(Range(Cells(5, 1).Address, Cells(65536, 1).Address), "<>")
'Col E
Totl = Totl + Application.WorksheetFunction.CountIf(Range(Cells(5, 5).Address, Cells(65536, 5).Address), "<>")
'remainder of Cols: Col # - 9 / Mod 4 = 0
For x = 9 To 253 Step 4
'add Col# countif to total
Totl = Totl + Application.WorksheetFunction.CountIf(Range(Cells(5, x).Address, Cells(65536, x).Address), "<>")
Next x
 
'return as sub
Range("A1") = Totl
End Sub

As for the last used Column - which row would you use to determine the Column? or perhaps add this:

 
'remainder of Cols: Col # - 9 / Mod 4 = 0
For x = 9 To 253 Step 4
 
'exit if Countif = 0
If Application.WorksheetFunction.CountIf(Range(Cells(5, x).Address, Cells(65536, x).Address), "<>") = 0 Then Exit For
 
'else add Col# countif to total
Totl = Totl + Application.WorksheetFunction.CountIf(Range(Cells(5, x).Address, Cells(65536, x).Address), "<>")
Next x

Cheers,

dr

[Bob Phillips]

[vba]

MsgBox ACtivesheet.Evaluate("=SUMPRODUCT(--(MOD(COLUMN(5:5),4)=1),--(5:5<>""""))")
[/vba]

[vzachin]

hi dr,

not quite sure i understand how your code works but it does.

thanks
zach

[vzachin]

hi xld,

the formula counted across row 5, but didn't total down the columns. anyway of modifying it?

thanks
zach

[Bob Phillips]

You requirement was stated as

... What I want is to count all the entries beginning in column A5 and then column E5 and every 4th column thereafter. (A5,E5,I5,M5..etc until the last column IS5)

that IS only row 5 as provided.

[rbrhodes]

xld.

I understood all of the entries in Column A from row 5 to row 655365 , then E5:E65536 then mod 4 the remaining columns.

What I don't know is if the totals are supposed to appear for each column and/or one grand total for all, I took it to mean one Grand total in my code.


Cheers,

dr


Cheers,

dr

[vzachin]

hi dr.

1 grand total ($1000) coming at you.



thanks again
zach