Challenge with vba countifs

P

PatrickK777777

New Member
Joined
Apr 3, 2024
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Am having a challenge here. Trying to use the application.worksheetfunction.countifs without success in a particular scenario. I want to count all values in range b5:b10000 greater than or equal to a date-type variable 'SmallestDate'. I've tried criteria like ">=" & SmallestDate', ">=SmallestDate" but am getting a result like as if the the matching values do not exist. Any ideas/answers are highly valued🙏
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
WOW. Ok maybe this is not 100% correctly formatted, but it is the best I can do for now. I gave up trying to shorten some of the lines (using line continuation characters) part way through. Trying to make dinner at the same time (I'm chief cook and bottle washer as they say). So I can't get back to the original issue for a while. HTH someone in the meantime.
VBA Code: 
Dim OpeningBalance As Double, ObservedBalance As Double, CalculatedBalance As Double
Dim TotalImbalance As Double, TotalCalculatedBalance As Double, DateInFlows As Double
Dim DatelOutFlows As Double, DateDebtors As Double, DateCreditors As Double
Dim SmallestDate As Date, BiggestDate As Date, DateToWorkOn As Date
Dim lastRowInIncomesColumn As Long, lastRowInExpensesColumn As Long
Dim ReferenceBook As String, EnteredValue As String

Set ws = ThisWorkbook.Sheets("sheet1")
Sheet1BalancingMethod = ""

 'get user's desire to balance off the book
Response = MsgBox("You have initiated balancing off of this work-sheet. Do you want to continue?", vbQuestion + vbYesNo, "Confirmation")
If Response = vbYes Then
    'INITIATE DATE ORDER
    ws.Range("BD1:BG1").Calculate
    'get biggest date
    If ws.Cells(1, "BE").Value > ws.Cells(1, "BG").Value Then
        BiggestDate = CDate(ws.Cells(1, "BE").Value)
    Else
        BiggestDate = CDate(ws.Cells(1, "BG").Value)
    End If
    'get smallest date
    If ws.Cells(1, "BD").Value <= ws.Cells(1, "BF").Value And ws.Cells(1, "BD").Value <> 0 Then
        SmallestDate = CDate(ws.Cells(1, "BD").Value)
    ElseIf ws.Cells(1, "BF").Value <= ws.Cells(1, "BD").Value And ws.Cells(1, "BF").Value <> 0 Then
        SmallestDate = CDate(ws.Cells(1, "BF").Value)
    ElseIf ws.Cells(1, "BD").Value >= ws.Cells(1, "BF").Value And ws.Cells(1, "BF").Value = 0 Then
        SmallestDate = CDate(ws.Cells(1, "BD").Value)
    ElseIf ws.Cells(1, "BF").Value >= ws.Cells(1, "BD").Value And ws.Cells(1, "BD").Value = 0 Then
        SmallestDate = CDate(ws.Cells(1, "BF").Value)
    End If
    
    If ws.Cells(1, "BD").Value = 0 And ws.Cells(1, "BF").Value = 0 Then
        SmallestDate = BiggestDate
    End If

    'Set REFERENCE BOOK
    If RemoveRepeatingSpaces(ws.Cells(3, "P").Value) = "" Then
        ReferenceBook = InputBox("Enter the reference book U want to balance.")
    Else
        ReferenceBook = RemoveRepeatingSpaces(ws.Cells(3, "P").Value)
    End If

    'Check for presence of multiple reference books
    If Application.WorksheetFunction.CountIfs(ws.Range("DA6:DA1048576"), ReferenceBook) _ 
       & + Application.WorksheetFunction.CountIfs(ws.Range("DK6:DK1048576"), ReferenceBook) _ 
       & <> Application.WorksheetFunction.CountA(ws.Range("DA6:DA1048576")) _ 
       & + Application.WorksheetFunction.CountA(ws.Range("DK6:DK1048576")) Then
           ReferenceBook = RemoveRepeatingSpaces(InputBox("Multiple reference books found. Please confirm the reference book which you are balancing off."))
    End If

    If Application.WorksheetFunction.CountIfs(ws.Range("DA6:DA1048576"), ReferenceBook) _
       & + Application.WorksheetFunction.CountIfs(ws.Range("DK6:DK1048576"), ReferenceBook) = 0 Then
          MsgBox ("The reference book provided has no entries made under it.")
          Exit Sub
    End If

    If RemoveRepeatingSpaces(ReferenceBook) = "" Then
        MsgBox ("Reference book is null.")
        Exit Sub
    End If

    'Initialise values to paste at the end of procedure
    TotalImbalance = 0
    TotalCalculatedBalance = 0

    'Check user desired balancing method
    If SmallestDate <> BiggestDate Then
        UserFormForBalancingOffSheet1.Show
        'In case date is only one
    Else
        DateToWorkOn = BiggestDate
        GoTo GeneralisedBalancing
    End If

    InvalidEntryHandlingPoint:
    If Err.Number <> 0 Then
        MsgBox ("Invalid value.")
        Err.Clear
        ws.Cells(3, "X").Calculate
        Exit Sub
    End If

    On Error GoTo 0
    'Update date ranges
    If Sheet1BalancingMethod = "DATE-RANGE-ONE-BY-ONE" Or Sheet1BalancingMethod = "DATE-RANGE-ALL-AT-ONCE" Then
        On Error GoTo InvalidEntryHandlingPoint
        SmallestDate = DateValue(InputBox("Enter desired start date"))
        BiggestDate = DateValue(InputBox("Enter desired end date"))
        On Error GoTo 0
    End If

    'If desiring ONE-BY-ONE DATE BALANCING
    If Sheet1BalancingMethod = "ALL-DATES-ONE-BY-ONE" Or Sheet1BalancingMethod = "DATE-RANGE-ONE-BY-ONE" Then
        For DateToWorkOn = SmallestDate To BiggestDate
            'In case the date has desired data
            If Application.WorksheetFunction.CountIfs(ws.Range("DA6:DA1048576"), ReferenceBook, _
               & ws.Range("A6:A1048576"), DateToWorkOn) + Application.WorksheetFunction.CountIfs(ws.Range("DK6:DK1048576"), _
               & ReferenceBook, ws.Range("P6:P1048576"), DateToWorkOn) > 0 Then
                    On Error GoTo InvalidEntryHandlingPoint
                    OpeningBalance = Evaluate(InputBox("Enter the Opening balance for " & DateToWorkOn))
                    ObservedBalance = Evaluate(InputBox("Enter the observed closing balance for " & DateToWorkOn))
                    On Error GoTo 0
                    DateInFlows = Application.WorksheetFunction.SumIfs(ws.Range("H6:H1048576"), _
                       ws.Range("A6:A1048576"), DateToWorkOn, ws.Range("DA6:DA1048576"), ReferenceBook)
                    DateOutFlows = Application.WorksheetFunction.SumIfs(ws.Range("T6:T1048576"), _
                    & ws.Range("P6:P1048576"), DateToWorkOn, ws.Range("DK6:DK1048576"), ReferenceBook)
                    DateDebtors = Application.WorksheetFunction.SumIfs(ws.Range("J6:J1048576"), ws.Range("A6:A1048576"), _ 
                       DateToWorkOn, ws.Range("DA6:DA1048576"), ReferenceBook)
                    DateCreditors = Application.WorksheetFunction.SumIfs(ws.Range("V6:V1048576"), ws.Range("P6:P1048576"), _
                       DateToWorkOn, ws.Range("DK6:DK1048576"), ReferenceBook)
                    CalculatedBalance = OpeningBalance + DateInFlows - DateDebtors - DateOutFlows + DateCreditors
                    If CalculatedBalance < ObservedBalance Then
                        'get the last row
                        lastRowInIncomesColumn = ws.Cells(ws.Rows.count, "C").End(xlUp).Row
                        If lastRowInIncomesColumn < 5 Then
                            lastRowInIncomesColumn = 5
                        End If
                        ws.Cells(lastRowInIncomesColumn + 1, "A").Value = DateToWorkOn
                        ws.Cells(lastRowInIncomesColumn + 1, "C").Value = "Unexplained income"
                        ws.Cells(lastRowInIncomesColumn + 1, "D").Value = 1
                        ws.Cells(lastRowInIncomesColumn + 1, "H").Value = ObservedBalance - CalculatedBalance
                        ws.Cells(lastRowInIncomesColumn + 1, "N").Value = _
                           & "Bal-b/f=" & OpeningBalance & " " & "Obs.Bal=" & ObservedBalance & " " & "Calc.Bal=" & CalculatedBalance
                        ws.Cells(lastRowInIncomesColumn + 1, "O").Value = 0
                        ws.Cells(lastRowInIncomesColumn + 1, "DA").Value = ReferenceBook
                        ws.Cells(lastRowInIncomesColumn + 1, "A").EntireRow.Calculate
                    ElseIf CalculatedBalance > ObservedBalance Then
                        'get the last row
                        lastRowInExpensesColumn = ws.Cells(ws.Rows.count, "R").End(xlUp).Row
                        If lastRowInExpensesColumn < 5 Then
                            lastRowInExpensesColumn = 5
                        End If
                        ws.Cells(lastRowInExpensesColumn + 1, "P").Value = DateToWorkOn
                        ws.Cells(lastRowInExpensesColumn + 1, "R").Value = "Unexplained deficit"
                        ws.Cells(lastRowInExpensesColumn + 1, "S").Value = 1
                        ws.Cells(lastRowInExpensesColumn + 1, "T").Value = CalculatedBalance - ObservedBalance
                        ws.Cells(lastRowInExpensesColumn + 1, "Z").Value = "Bal-b/f=" & OpeningBalance & " " & "Obs.Bal=" & ObservedBalance & " " & "Calc.Bal=" & CalculatedBalance
                        ws.Cells(lastRowInExpensesColumn + 1, "AA").Value = 0
                        ws.Cells(lastRowInExpensesColumn + 1, "DK").Value = ReferenceBook
                        ws.Cells(lastRowInExpensesColumn + 1, "A").EntireRow.Calculate
                    End If
                TotalImbalance = TotalImbalance + (ObservedBalance - CalculatedBalance)
                TotalCalculatedBalance = TotalCalculatedBalance + CalculatedBalance
            End If

        Next DateToWorkOn

    'If desiring AT-ONCE BALANCING
    ElseIf Sheet1BalancingMethod = "ALL-DATES-ONE-BY-ONCE" Or Sheet1BalancingMethod = "DATE-RANGE-ALL-AT-ONCE" Then
        GeneralisedBalancing:
        On Error GoTo InvalidEntryHandlingPoint
        OpeningBalance = Evaluate(InputBox("Enter the total Opening balance"))
        ObservedBalance = Evaluate(InputBox("Enter the total Observed closing balance"))
        On Error GoTo 0
        DateInFlows = Application.WorksheetFunction.SumIfs(ws.Range("H6:H1048576"), ws.Range("A6:A1048576"), ">=SmallestDate", ws.Range("A6:A1048576"), "<=BiggestDate", ws.Range("DA6:DA1048576"), ReferenceBook)
        DateOutFlows = Application.WorksheetFunction.SumIfs(ws.Range("T6:T1048576"), ws.Range("P6:P1048576"), ">=SmallestDate", ws.Range("P6:P1048576"), "<=BiggestDate", ws.Range("DK6:DK1048576"), ReferenceBook)
        DateDebtors = Application.WorksheetFunction.SumIfs(ws.Range("J6:J1048576"), ws.Range("A6:A1048576"), ">=SmallestDate", ws.Range("A6:A1048576"), "<=BiggestDate", ws.Range("DA6:DA1048576"), ReferenceBook)
        DateCreditors = Application.WorksheetFunction.SumIfs(ws.Range("V6:V1048576"), ws.Range("P6:P1048576"), ">=SmallestDate", ws.Range("P6:P1048576"), "<=BiggestDate", ws.Range("DK6:DK1048576"), ReferenceBook)
        CalculatedBalance = OpeningBalance + DateInFlows - DateDebtors - DateOutFlows + DateCreditors
        If CalculatedBalance < ObservedBalance Then
            'get the last row
            lastRowInIncomesColumn = ws.Cells(ws.Rows.count, "C").End(xlUp).Row
            If lastRowInIncomesColumn < 5 Then
                lastRowInIncomesColumn = 5
            End If
            ws.Cells(lastRowInIncomesColumn + 1, "A").Value = BiggestDate
            ws.Cells(lastRowInIncomesColumn + 1, "C").Value = "Unexplained income"
            ws.Cells(lastRowInIncomesColumn + 1, "D").Value = 1
            ws.Cells(lastRowInIncomesColumn + 1, "H").Value = ObservedBalance - CalculatedBalance
            ws.Cells(lastRowInIncomesColumn + 1, "N").Value = "Bal-b/f=" & OpeningBalance & " " & "Obs.Bal=" & ObservedBalance & " " & "Calc.Bal=" & CalculatedBalance
            ws.Cells(lastRowInIncomesColumn + 1, "O").Value = 0
            ws.Cells(lastRowInIncomesColumn + 1, "DA").Value = ReferenceBook
            ws.Cells(lastRowInIncomesColumn + 1, "A").EntireRow.Calculate
        ElseIf CalculatedBalance > ObservedBalance Then
            'get the last row
            lastRowInExpensesColumn = ws.Cells(ws.Rows.count, "R").End(xlUp).Row
            If lastRowInExpensesColumn < 5 Then
                lastRowInExpensesColumn = 5
            End If
            ws.Cells(lastRowInExpensesColumn + 1, "P").Value = BiggestDate
            ws.Cells(lastRowInExpensesColumn + 1, "R").Value = "Unexplained deficit"
            ws.Cells(lastRowInExpensesColumn + 1, "S").Value = 1
            ws.Cells(lastRowInExpensesColumn + 1, "T").Value = CalculatedBalance - ObservedBalance
            ws.Cells(lastRowInExpensesColumn + 1, "Z").Value = "Bal-b/f=" & OpeningBalance & " " & "Obs.Bal=" & ObservedBalance & " " & "Calc.Bal=" & CalculatedBalance
            ws.Cells(lastRowInExpensesColumn + 1, "AA").Value = 0
            ws.Cells(lastRowInExpensesColumn + 1, "DK").Value = ReferenceBook
            ws.Cells(lastRowInExpensesColumn + 1, "A").EntireRow.Calculate
        End If
    TotalImbalance = TotalImbalance + (ObservedBalance - CalculatedBalance)
    TotalCalculatedBalance = TotalCalculatedBalance + CalculatedBalance
        'CANCEL
    ElseIf Sheet1BalancingMethod = "CANCEL" Then
        Exit Sub
    End If

    'Reset balancing method
    Sheet1BalancingMethod = ""
    ws.Cells(3, "S").Value = TotalCalculatedBalance
    ws.Cells(3, "X").Calculate
    MsgBox ("Total Calculated cash balance is " & TotalCalculatedBalance)

    If TotalImbalance >= 0 Then
        MsgBox ("Net unexplained surplus is " & TotalImbalance)
    Else
        MsgBox ("Net unexplained deficit is " & Right(TotalImbalance, Len(TotalImbalance) - 1))
    End If

End If

End Sub
 
Upvote 1
Try following this format and let us know how you go:

VBA Code: 
DateInFlows = Application.WorksheetFunction.SumIfs(ws.Range("H6:H1048576"), _
                ws.Range("A6:A1048576"), ">=" & CLng(SmallestDate), _
                ws.Range("A6:A1048576"), "<=" & CLng(BiggestDate), _
                ws.Range("DA6:DA1048576"), ReferenceBook)
 
Upvote 1
Solution
VBA generally works with US Date format. By using CLng we are working with the underlying number and that takes the date format issue out of the equation.
If you want to put the cell value straight into the formula using .Value2 will also avoid the formatting issue.

The only other way I managed to make it work is to feed the date into the formula using the US date format
eg
Rich (BB code): 
cnt = WorksheetFunction.CountIfs(Range("$A$7:$A$15"), ">=" & Format(dt, "mm/dd/yyyy"))
 
Upvote 1
is smallestdate a named range in your workbook or a variable in your CODE.

It may be a good idea to post your code where you declare and use the function and variable(s).
 
Upvote 0
try ">=#" & SmallestDate & "#"
The single quote in your first example ought to be a problem for sure.

More of the code would probably help, especially the Dim statements. Might as well post the whole procedure if you do (please use vba button on posting toolbar and paste between the resulting code tags).
 
Upvote 0
awoohaw said:
is smallestdate a named range in your workbook or a variable in your CODE.

It may be a good idea to post your code where you declare and use the function and variable(s).
Click to expand...
Date variable from
Dim SmallestDate as date

Entire Procedure quite long and am not familiar with this platform.
 
Upvote 0
as @Micron wrote... copy your procedure... and use the "VBA" quoting button above in each POST editing bar.

1712177412067.png
 
Upvote 0
awoohaw said:
is smallestdate a named range in your workbook or a variable in your CODE.

It may be a good idea to post your code where you declare and use the function and variable(s).
Click to expand...
Sorry single quote does not exist. It's meant to be ">=" & SmallestDate or ">=SmallestDate". Let me put the # and see the outcome.
 
Upvote 0
The following mini-sub works as intended:
VBA Code: 
Sub Test()
    Dim SmallestDate As Date
    SmallestDate = #1/3/2024#
    MsgBox WorksheetFunction.CountIfs([B5:B10000], ">=" & SmallestDate)
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Challenge with countif limitations
2
Replies
14
Views
232
PatrickK777777
P
W
  • Solved
Countif multiple columns
Replies
2
Views
192
windoz
W
Knockoutpie
  • Question
Excel Formula to check if two strings exist in the same row on a sheet without specifying the column
Replies
2
Views
267
Knockoutpie
Knockoutpie
I
  • Question
CountIF alternative for performance improvements
2 3
Replies
21
Views
420
ItalianPlatinum
I
E
  • Solved
COUNTIF and SUMIF formula with multiple criteria
Replies
2
Views
206
excelnewb221
E

Forum statistics

Threads
1,216,014
Messages
6,128,280
Members
449,436
Latest member
blaineSpartan

Share this page

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
Back
Top