ScotlandsNeo
New Member
- Joined
- Nov 12, 2015
- Messages
- 10
Hi all,
I am trying to use application.worksheetfunction.countifs in order to do a quick countifs across 4 columns where the follwing criteria is met.
Column 1 (ref_Col) has values lower than £50
columns 2,3 and 4 have no entries and are blank cells.
If the above condition is not met then do not count.
so far i have the following code
LR is just my variable for last row where it counts up from lastrow '.end(xlup).row'
v1 is variable that is taking the value from a ccell, in this case '50'
val1 is the variable i want to set that gives me my countifs result and i can then use that to paste into my sheet directly.
this code counts correctly that my column 'REF_COL' has 3 values less than £50. How do i extend this so that it also checks columns 2,3 and 4 have no entries adjacent to the values, and if they do then not to count it.
I am trying to use application.worksheetfunction.countifs in order to do a quick countifs across 4 columns where the follwing criteria is met.
Column 1 (ref_Col) has values lower than £50
columns 2,3 and 4 have no entries and are blank cells.
If the above condition is not met then do not count.
so far i have the following code
LR is just my variable for last row where it counts up from lastrow '.end(xlup).row'
v1 is variable that is taking the value from a ccell, in this case '50'
val1 is the variable i want to set that gives me my countifs result and i can then use that to paste into my sheet directly.
Code:
val1 = application.worksheetfunctions.countifs(Range(Sheets(1).cells(1, ref_col), sheets(1).cells (LR, Ref_Col)), "<" & v1)
this code counts correctly that my column 'REF_COL' has 3 values less than £50. How do i extend this so that it also checks columns 2,3 and 4 have no entries adjacent to the values, and if they do then not to count it.
Last edited: