Count the cells in certain specific column with condition

[Surajitneog]

Sir
In a table, how can I count the number of cells (row wise) in certain specific columns (say column no 1, 4, 8, 12 etc) which meets certain given conditions (e.g value >100).

Thanks

Surjitneog

 

[JamesW]

Hi there,

Assuming your cols always go up in 4's, try:

Sub test()
    For i = 1 To 12 Step 4
        If i = 5 Then i = 4
        If Cells(4, i).Value > 100 Then x = x + 1
    Next
    Range("a1").Value = x
End Sub

 

[Surajitneog]

Hi there,

Assuming your cols always go up in 4's, try:

Sub test()
    For i = 1 To 12 Step 4
        If i = 5 Then i = 4
        If Cells(4, i).Value > 100 Then x = x + 1
    Next
    Range("a1").Value = x
End Sub

Hi James
Thanks for the answer. You are using macro here. Cannot you solve the problem in simple excel formula? I am not very much conversant with macro.

Surajitneog

 

[theta]

Let's assume A1:A10 contain numbers. Place this into B1

=COUNTIF(A1:A10,">100")

There are more advanced methods such as INDEX/MATCH and SUMPRODUCT that will allow for multiple criteria based on neighbouring column values etc

 

[Shalbriri]

Countif would be the simpler version of it.

 

[Surajitneog]

Hi
Probably u r not going through my question properly. In want the no. of cells in certain specific columns and not in the entire range of columns of the table.

Thanks
Surajitneog

 

[Shalbriri]

for each cell you could use =CountIfs

tho still have to specify a range...