VBA Code for countifs isnt working

Rant · Jul 3, 2021

Hi all,

I am trying to get the macros to populate Cells(7, Column) with total hours worked by the contractor {sum value of number of (D x 12 +N x 12)} for each day from specified Start date to End date( based on Cells(5, Column) dates)

Would really appreciate if you could please help me on with this.

Thank you.

Workbook name: Roster-2021

Worksheet2 name: Summary

Cell ‘A7’ content is Contractor

Worksheet3 name: Roster

Range("D3:D200") has the following details: Permanent Employee, Temporary Employee and contractor

Range("F1:PA1") has dates from 01- January-2021 to 31-December-2021

Range("F4:PA200") has D and N. (D is Day shift and N is Night Shift)

The following code works fine:

Cells(5, Column) = StartD + Column - 3

Cells(6, Column) = Day(StartD + Column - 3)

Countifs code seems incorrect.

VBA Code:

VBA Code:

Private Sub CommandButton3_Click()

Application.ScreenUpdating = False



If CDate(TextBox2.Value) < CDate(TextBox1.Value) Then

MsgBox "Invalid entry! Start date must be < than End Date."

End If



Dim StartD As Date, EndD As Date

StartD = TextBox1.Value

EndD = TextBox2.Value

For Column = 3 To EndD - StartD + 3

Cells(5, Column) = StartD + Column - 3

Cells(6, Column) = Day(StartD + Column - 3)



Dim Con As String

Dim she2 As Worksheet

Dim she3 As Worksheet

Dim refD As Date



refD = StartD + Column - 3

Con = Range("A7").Text

she2 = Worksheets("Summary")

she3 = Worksheets("Roster")



she2.Cells(7, Column) = Application.Worksheet.CountIfs(she3!Range("D3:D200"), she2!Con, she3!Range("F1:PA1"), she2!refD, she3!Range("F4:PA200"), "D") + Application.Worksheet.CountIfs(she3!Range("D3:D200"), she2!Con, she3!Range("F1:PA1"), she2!refD, she3!Range("F4:PA200"), "N")



Next Column



Application.ScreenUpdating = True

End Sub

GWteB · Jul 4, 2021

Welcome to the MrExcel Message Board!

Since you're not telling us where you get stuck, we have to guess. Usually we are not successful in that. A quick look at your code yields the following.
Worksheets are in VBA terms Objects. Assigning an Object to a custom variable needs to be done using the Set statement.
So a code line like:

Rich (BB code):

she2 = Worksheets("Summary")

would be:

VBA Code:

Set she2 = Worksheets("Summary")

Hopefully this is of some help.

Alex Blakenburg · Jul 4, 2021

I think we need to address the actual formula before doing too much else.
The initial requirement sounds like more like a sumifs than a countifs.
The formula itself has inconsistent range lengths and mixes vertical and horizontal ranges.
(all columns should be 3:200
and F1:PA1 will most likely need an Index/Match and a vertical criteria or is the the sum range)

Can we get an XL2BB of she3 & she2 and an example of the expected result ?
Step 1 is to get the formula working in Excel before incorporating it into VBA.

(I have reformatted the formula in the quote for clarity)

Rant said:
she2 = Worksheets("Summary")
she3 = Worksheets("Roster")

she2.Cells(7, Column) = Application.Worksheet.CountIfs(she3!Range("D3:D200"), she2!Con, _
she3!Range("F1:PA1"), she2!refD, _
she3!Range("F4:PA200"), "D") + _
Application.Worksheet.CountIfs(she3!Range("D3:D200"), she2!Con, _
she3!Range("F1:PA1"), she2!refD, _
she3!Range("F4:PA200"), "N")

Rant · Jul 5, 2021

Rant said:
he2.Cells(7, Column) = Application.Worksheet.CountIfs(she3!Range("D3:D200"), she2!Con, she3!Range("F1:PA1"), she2!refD, she3!Range("F4:PA200"), "D") + Application.Worksheet.CountIfs(she3!Range("D3:D200"), she2!Con, she3!Range("F1:PA1"), she2!refD, she3!Range("F4:PA200"), "N")

Thank you GWteB and Alex for looking into this.

When I click the generate button, Summary!Cells(5, Column) gets populated with dates.

I'm getting stuck with the vba code (countifs) to count total hours worked by Non-working visitors-contractor {sum value of number of (D x 12 +N x 12)} for each date from specified Start date to End date (Cells(5, Column) when I click the generate button.

I am trying to get the macros to do countifs by:
finding Non-working visitors-contractor (C7 value) from "Roster!Range("D3:D200") and date (Cells(5, Column)) say 1st May from "Roster!Range("F1:PA1")"
count all the D's and N's from "Roster! Range("F4:PA200")" that match the two (C7 value- Non-working visitors-contractor, date in Summary!Cells(5, Column) dates)) values.
convert it to hours by D x 12 +N x 12.

Alex Blakenburg · Jul 6, 2021

Not providing XL2BB makes it really hard to provide assistance.
I assume the 2nd image is the Roster sheet.

The first step is to get a working formula just using Excel.
I was unable to get countifs to do what you want and ended up using Sumproduct.

See if you can get it working at your end before trying to incorporate it into VBA.
I have used Range Names which mimick the variables you are using in VBA.

20210706 Countif contiguous columns with VBA v02 SumProduct.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

StartD

3/05/2021

1/05/2021

2/05/2021

3/05/2021

4/05/2021

5/05/2021

6/05/2021

7/05/2021

8/05/2021

9/05/2021

2

EndD

5/05/2021

Contractor Site Personnel

N

D

N

D

N

D

3

Contractor Site Personnel

D

N

D

N

D

N

D

4

Countifs

Contractor Site Personnel

D

N

D

N

D

N

5

N

14

Contractor Site Personnel

D

N

D

N

D

N

D

6

D

23

Contractor Site Personnel

N

D

N

D

N

D

7

Total

37

Contractor Site Personnel

D

N

D

8

Contractor Site Personnel

D

N

9

Contractor Site Personnel

N

D

10

Other Criteria (Con)

Contractor Site Personnel

Other

N

D

N

11

Other

D

N

D

12

Contractor Site Personnel

X

N

D

13

Contractor Site Personnel

X

D

N

14

Contractor Site Personnel

N

D

15

Contractor Site Personnel

D

N

16

Contractor Site Personnel

D

N

D

Roster

Cell Formulas
Range	Formula
B5:B6	B5	=SUMPRODUCT(($D$1:$D$204=Con)*(INDEX($F$1:$PA$204,0,MATCH(StartD,$F$1:$PA$1,0)):INDEX($F$1:$PA$204,0,MATCH(EndD,$F$1:$PA$1,0))=$A5))
B7	B7	=SUM(B5:B6)

Named Ranges
Name	Refers To	Cells
Con	=Roster!$B$10	B5:B6
EndD	=Roster!$B$2	B5:B6
StartD	=Roster!$B$1	B5:B6

Rant · Jul 7, 2021

Awesome. That works. Thanks mate.

However, when I add this to vba, I get Run-time error 438! "Object doesn't support this property or method"

"Summary" sheet Code:

Dim Ros As Worksheet

Set Ros = Worksheets("Roster")

Cells(7, Column) = WorksheetFunction.SumProduct((Ros!Range(D1, D154) = A7) * (WorksheetFunction.Index(Ros!Range(F1, PA154), 0, WorksheetFunction.Match(Cells(5, Column), Ros!Range(F1, PA1), 0)) = "D")) + WorksheetFunction.SumProduct((Ros!Range(D1, D154) = A7) * (WorksheetFunction.Index(Ros!Range(F1, PA154), 0, WorksheetFunction.Match(Cells(5, Column), Ros!Range(F1, PA1), 0)) = "N"))

Alex Blakenburg · Jul 7, 2021

Do you just want the result of the formula in Cells(7, Column) or the formula itself ?
Although the current syntax is indicates the result, I suspect you want the formula to appear in the cell.

Alex Blakenburg · Jul 7, 2021

Sorry I couldn't get the countifs to work initially because I mistakenly assumed you wanted to add the whole date range in the one formula.
Since I think you want to put the formula in each cell, for each day, I have gone back to it since it should be faster than sumproduct.
I might have made it bit more complicated than it needs to be but see how you go with it.

Before the For statement my formula needs this.

VBA Code:

        Set she2 = Worksheets("Summary")
        Set Ros = Worksheets("Roster")
        
        Dim LastRowNo As Long
        Dim sLastCol As String
              
        LastRowNo = 154
        sLastCol = "PA"

Inside the Loop this

VBA Code:

        she2.Cells(7, Column).Formula = "=COUNTIFS(Roster!$D$1:$D$" & LastRowNo & ",$A7," & _
                                           "INDEX(Roster!$F$1:$" & sLastCol & "$" & LastRowNo & _
                                             ",0,MATCH(" & she2.Cells(5, Column).Address(True, False) & ",Roster!$F$1:$" & sLastCol & "$1,0)),""D"")" & _
                                        "+COUNTIFS(Roster!$D$1:$D$" & LastRowNo & ",$A7," & _
                                           "INDEX(Roster!$F$1:$" & sLastCol & "$" & LastRowNo & _
                                             ",0,MATCH(" & she2.Cells(5, Column).Address(True, False) & ",Roster!$F$1:$" & sLastCol & "$1,0)),""N"")"

Rant · Jul 7, 2021

Mate, you are awesome! That was exactly what I was looking for.
Thank you so much.

Alex Blakenburg · Jul 7, 2021

Thanks for letting us know. Glad we could help.

VBA Code for countifs isnt working

Rant

New Member

Excel Facts

GWteB

Well-known Member

Alex Blakenburg

MrExcel MVP

Rant

New Member

Alex Blakenburg

MrExcel MVP

Rant

New Member

Alex Blakenburg

MrExcel MVP

Alex Blakenburg

MrExcel MVP

Rant

New Member

Alex Blakenburg

MrExcel MVP

Similar threads

Forum statistics

Share this page

VBA Code for countifs isnt working

New Member

Excel Facts

Well-known Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock