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

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,020
Office Version
  1. 2016
Platform
  1. Windows
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.

Thanks in advance.
(Kelly mort)
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,704
Office Version
  1. 365
Platform
  1. Windows
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
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,020
Office Version
  1. 2016
Platform
  1. Windows
@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
Joined
Apr 10, 2017
Messages
2,020
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Feb 23, 2021
Messages
1,704
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,434
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
Joined
Apr 10, 2017
Messages
2,020
Office Version
  1. 2016
Platform
  1. Windows
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.

I appreciate your time.

Have a wonderful time
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,704
Office Version
  1. 365
Platform
  1. Windows
Glad we could help.

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")
 

Forum statistics

Threads
1,141,400
Messages
5,706,217
Members
421,433
Latest member
yash0468

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