Count the cells in certain specific column with condition

Surajitneog

New Member
Joined
Mar 14, 2011
Messages
3
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

JamesW

Well-known Member
Joined
Oct 30, 2009
Messages
1,197
Hi there,

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

Code:
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

New Member
Joined
Mar 14, 2011
Messages
3
Hi there,

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

Code:
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

Well-known Member
Joined
Jun 9, 2009
Messages
960
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

Board Regular
Joined
Nov 4, 2010
Messages
93

ADVERTISEMENT

Countif would be the simpler version of it.
 

Surajitneog

New Member
Joined
Mar 14, 2011
Messages
3
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

Board Regular
Joined
Nov 4, 2010
Messages
93
for each cell you could use =CountIfs

tho still have to specify a range...
 

Watch MrExcel Video

Forum statistics

Threads
1,123,255
Messages
5,600,553
Members
414,388
Latest member
Pkmep4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top