# Best way to use a countif function or best approach to write a logical argument needed - VBA

#### kelly mort

##### Well-known Member
I am stacked with this script and I need help to make it happen.

I have 3 sheets that I want to look at a given column for each of them say column B and count for values greater than zero (0) in the said column.

The code I have currently looks like this:

Code:
``````If Countif  (sheet 1 col B, ">0") >0 And Countif  (sheet 2 col B, ">0") >0 And Countif  (sheet 3 col B, ">0") >0 Then
MyVar = Sum ( sheet 3 col C)
ElseIf Countif  (sheet 1 col B, ">0") >0 And Countif  (sheet 2 col B, ">0") >0 And Countif  (sheet 3 col B, ">0") =0 Then
MyVar = Sum (sheet 2 col C)
ElseIf Countif  (sheet 1 col B, ">0") >0 And Countif  (sheet 2 col B, ">0") = 0 And Countif  (sheet 3 col B, ">0") = 0 Then
MyVar = Sum (sheet 1 col C)
Else
MyVar = Sum (sheet 1 col C)
End If``````

Then when there was no data on sheet 1 it failed to output the sum from the sheet 2.
I could try and add more conditions to the if function but I want to find out if there are better ways of achieving this before I try making the if conditions complicated again.

(Kelly mort)

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### Alex Blakenburg

##### Well-known Member
I don'tknow that it makes sense to have the last ElseIf and then the Else both returning the same value, it means you don't need to the last ElseIf.
I have left it the way it is, see if this works for you.
I am also not sure of what the point is in checking for positive values in Column B but not using a SumIf of positive values in the sum function.

See if this is what you want.

VBA Code:
``````Sub CheckForPositives()

Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim sh1PosCnt As Long
Dim sh2PosCnt As Long
Dim sh3PosCnt As Long
Dim MyVar As Double

Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
Set sh3 = Worksheets("Sheet3")

sh1PosCnt = WorksheetFunction.CountIf(sh1.Range("B:B"), ">0")
sh2PosCnt = WorksheetFunction.CountIf(sh2.Range("B:B"), ">0")
sh3PosCnt = WorksheetFunction.CountIf(sh3.Range("B:B"), ">0")

MyVar = 0

If sh1PosCnt > 0 And sh2PosCnt > 0 And sh3PosCnt > 0 Then
MyVar = WorksheetFunction.Sum(sh3.Range("C:C"))
ElseIf sh1PosCnt > 0 And sh2PosCnt > 0 And sh3PosCnt = 0 Then
MyVar = WorksheetFunction.Sum(sh2.Range("C:C"))
ElseIf sh1PosCnt > 0 And sh2PosCnt = 0 And sh3PosCnt = 0 Then
MyVar = WorksheetFunction.Sum(sh1.Range("C:C"))
Else
MyVar = WorksheetFunction.Sum(sh1.Range("C:C"))
End If

End Sub``````

#### AlphaFrog

##### MrExcel MVP
Then when there was no data on sheet 1 it failed to output the sum from the sheet 2.
I didn't understand the statement above.
What are all your conditions? The pattern is not clear from your code.

#### kelly mort

##### Well-known Member
@Alex Blakenburg ,
You are very right about the last if condition. I don't actually need it I only placed it there in case some extremes come my way. Lol.

And with the case of using a sumif function against the column C, I am glad you pointed that out. It would help me a lot. Thanks for that tip.

The code you wrote, I have a similar syntax, which is not giving me the desired output.

When I planned the logic, I never anticipated a situation where there would be no data on sheet 1 but have data on sheet 2 and or 3.

During the planning process, I thought there would always be data on sheet 1 before sheet 2 and sheet 3. Those forms of irregularities didn't cross my mind at the time.

When I used data I mean column B. That's when that column is blank, I describe it as the sheet has no data since that's where I am using to verify my data or no data.

And the case I am facing currently is that sheet 1 is blank but sheet 2 has data.

Therefore, none of the conditions were met - the code has no option than to point to the last condition (else) pointing me to the blank sheet (sheet 1).

That is what I need a way around.

In the above scenario (where sheet 1 is blank) I want to check if sheet 2 and 3 are both not blank. If both are not blank, then I want to use sheet 3 else use any of sheets 2 and 3 which is not blank.

#### kelly mort

##### Well-known Member

I didn't understand the statement above.
What are all your conditions? The pattern is not clear from your code.

What I want to do is to be able to point to a particular sheet from 3 sheets.

If all 3 sheets have data (value greater than 0 in column B), then I use sheet 3.

If only sheet 1 has data (value greater than 0) in column B, then I use sheet 1.

If both sheets 1 and 3 have data as described before, then I use sheet 3.

Using the numbers 1 2 3, one being the oldest and 3 being the newest or latest, my goal is to use the most recent sheet with data (value greater than 0) in column B.

#### Alex Blakenburg

##### Well-known Member
Using the numbers 1 2 3, one being the oldest and 3 being the newest or latest, my goal is to use the most recent sheet with data (value greater than 0) in column B.

That is a much clearer requirement statement and allows the code to be much simpler.
It still needs a decision on what to do if none of them have positive values in Column B.

VBA Code:
``````Sub UseMostRecentWithPositives()

Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim sh1PosCnt As Long
Dim sh2PosCnt As Long
Dim sh3PosCnt As Long
Dim MyVar As Double

Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
Set sh3 = Worksheets("Sheet3")

sh1PosCnt = WorksheetFunction.CountIf(sh1.Range("B:B"), ">0")
sh2PosCnt = WorksheetFunction.CountIf(sh2.Range("B:B"), ">0")
sh3PosCnt = WorksheetFunction.CountIf(sh3.Range("B:B"), ">0")

MyVar = 0

If sh3PosCnt > 0 Then
MyVar = WorksheetFunction.Sum(sh3.Range("C:C"))
ElseIf sh2PosCnt > 0 Then
MyVar = WorksheetFunction.Sum(sh2.Range("C:C"))
ElseIf sh1PosCnt > 0 Then
MyVar = WorksheetFunction.Sum(sh1.Range("C:C"))
Else
MyVar = WorksheetFunction.Sum(sh1.Range("C:C"))
End If

End Sub``````

#### kelly mort

##### Well-known Member

@Alex Blakenburg,

Thanks. It's fixed now.

#### AlphaFrog

##### MrExcel MVP
Using the numbers 1 2 3, one being the oldest and 3 being the newest or latest, my goal is to use the most recent sheet with data (value greater than 0) in column B.
Check the newest first and then the older ones.

VBA Code:
``````    If Application.CountIf(Sheets(3).Columns("B"), ">0") > 0 Then
MyVar = Application.Sum(Sheets(3).Columns("C"))
ElseIf Application.CountIf(Sheets(2).Columns("B"), ">0") > 0 Then
MyVar = Application.Sum(Sheets(2).Columns("C"))
ElseIf Application.CountIf(Sheets(1).Columns("B"), ">0") > 0 Then
MyVar = Application.Sum(Sheets(1).Columns("C"))
Else
'no data
End If``````

#### kelly mort

##### Well-known Member
Check the newest first and then the older ones.

VBA Code:
``````    If Application.CountIf(Sheets(3).Columns("B"), ">0") > 0 Then
MyVar = Application.Sum(Sheets(3).Columns("C"))
ElseIf Application.CountIf(Sheets(2).Columns("B"), ">0") > 0 Then
MyVar = Application.Sum(Sheets(3).Columns("C"))
ElseIf Application.CountIf(Sheets(1).Columns("B"), ">0") > 0 Then
MyVar = Application.Sum(Sheets(1).Columns("C"))
Else
'no data
End If``````
You guys are just amazing.

Have a wonderful time

#### Alex Blakenburg

##### Well-known Member

In case you need it:-
(sh3 appears twice and you would need to be changed for 2 & 1)

VBA Code:
``MyVar = WorksheetFunction.SumIfs(sh3.Range("C:C"), sh3.Range("B:B"), ">0")``

Replies
1
Views
48
Replies
8
Views
114
Replies
2
Views
31
Replies
0
Views
88
Replies
1
Views
80

1,141,403
Messages
5,706,257
Members
421,435
Latest member
Yoga15

### 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.

### Which adblocker are you using?

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

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