CountIf Help

hello

on topic: I have the same issue with counting dates...so I use countifs for multiple conditions (including date) and I don't understand why it count only to the 600 line in excel :(


Cnt11_ian = Application.WorksheetFunction.CountIfs _
(Sheets(1).Range("H7:H600" & LastRow1), ">=1/1/2014", Sheets(1).Range("H7:H600" & LastRow1), "<2/1/2014", _
Sheets(1).Range("D7:D600" & LastRow1), Sheets(1).Cells(i, "D"), _
Sheets(1).Range("A7:A600" & LastRow1), Sheets(1).Cells(i, "A")) '



if I count without takeing in account the date it can go to the last row in sheet
Cnt11_ian = Application.WorksheetFunction.CountIfs _
(Sheets(1).Range("D7:D" & LastRow1), Sheets(1).Cells(i, "D"), _
Sheets(1).Range("A7:A" & LastRow1), Sheets(1).Cells(i, "A")) '

anu ideea?

if LastRow1 is null then Range("H7:H600" & LastRow1) = Range("H7:H600")
if LastRow1 is 5 then Range("H7:H600" & LastRow1) = Range("H7:H6005")



after assigning a value to variable LastRow1 (such as LastRow1 = Cells(Rows.Count, 1).End(xlup).Row)

Code:

---

Range("H7:H" & LastRow1)

---

i don't understand :( why to put an if condition

bellow is my code

Code:

---

Sub Button1_Click()
 
Dim LastRow1 As Integer 

Dim LastRow2_ian  As Integer '
Dim i As Integer
Dim Cnt11_ian As Double 

Dim Cnt21_ian As Double 

 
Sheets(2).Range("C4:AL33") = Null
LastRow1 = Sheets(1).Cells(Rows.Count, "D").End(xlUp).Row

 

For i = 1 To LastRow1

    LastRow2_ian = Sheets(2).Cells(Rows.Count, "C").End(xlUp).Row

    Cnt21_ian = Application.WorksheetFunction.CountIfs _
        (Sheets(2).Range("C1:C" & LastRow2_ian), Sheets(1).Cells(i, "D"), _
        Sheets(2).Range("D1:D" & LastRow2_ian), Sheets(1).Cells(i, "A"))

    Cnt11_ian = Application.WorksheetFunction.CountIfs _
      (Sheets(1).Range("H7:H600" & LastRow1), ">=1/1/2014", Sheets(1).Range("H7:H600" & LastRow1), "<2/1/2014", _
        Sheets(1).Range("D7:D600" & LastRow1), Sheets(1).Cells(i, "D"), _
        Sheets(1).Range("A7:A600" & LastRow1), Sheets(1).Cells(i, "A")) 

 
              If Cnt11_ian >= 1 And Cnt21_ian = 0 Then

                                If Sheets(2).Cells(LastRow2_ian, "C") = "" Then 'cauta prima linie
                                    Sheets(2).Cells(LastRow2_ian + 4, "C") = Sheets(1).Cells(i, "D")
                                    Sheets(2).Cells(LastRow2_ian + 4, "D") = Sheets(1).Cells(i, "A")
                                    Sheets(2).Cells(LastRow2_ian + 4, "E") = Cnt11_ian
                                Else
                                    Sheets(2).Cells(LastRow2_ian + 1, "C") = Sheets(1).Cells(i, "D")
                                    Sheets(2).Cells(LastRow2_ian + 1, "D") = Sheets(1).Cells(i, "A")
                                    Sheets(2).Cells(LastRow2_ian + 1, "E") = Cnt11_ian
                                End If
              End If
Next
 
Sheets(2).Range("C29:E29") = Application.WorksheetFunction.SumIf(Sheets(2).Range("D4:D28"), "WP1", Sheets(2).Range("E4:E28"))
Sheets(2).Range("C30:E30") = Application.WorksheetFunction.SumIf(Sheets(2).Range("D4:D28"), "WP2", Sheets(2).Range("E4:E28"))
Sheets(2).Range("C31:E31") = Application.WorksheetFunction.SumIf(Sheets(2).Range("D4:D28"), "WP3", Sheets(2).Range("E4:E28"))
Sheets(2).Range("C32:E32") = Application.WorksheetFunction.SumIf(Sheets(2).Range("D4:D28"), "WP4", Sheets(2).Range("E4:E28"))
Sheets(2).Range("C33:E33") = Application.WorksheetFunction.SumIf(Sheets(2).Range("D4:D28"), "WP5", Sheets(2).Range("E4:E28"))

 end sub

---

apparently, the first two lines in my post are just an explanation of building a range with a variable and are not a part of a macro.

you should use the line Range("H7:H" & LastRow1) to correct the ranges in your code.

if you are willing to go any further, start your own thread or ask a moderator to split this thread into two and post your workbook.