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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

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...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,951
Messages
5,834,531
Members
430,295
Latest member
amdis

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