Countif and If functions

[MatthewLane412]

Hello,

I'm trying to pull data from sheet 1 and insert it into a particular cell in sheet 3. This has worked perfectly however I need to lay down a set of limitations. If Column "I" contains either "666","637", or "639" in a cell I don't want the countif statement to count that row.

Hopefully this isn't confusing.
My countif coding is down below.

Thanks,
Matt

Sub IncomeStatement()


Dim I As Long
    finRow = Sheet1.Range("A10000").End(xlUp).Row
    
I = 3


Sheet3.Range("B9") = Application.WorksheetFunction.CountIf(Range("AH3:AH" & finRow), "<365")
Sheet3.Range("B8") = Application.WorksheetFunction.CountIf(Range("AI3:AI" & finRow), "<365")
Sheet3.Range("B5") = Application.WorksheetFunction.CountIf(Range("AG3:AG" & finRow), "")
Sheet3.Range("B6") = Application.WorksheetFunction.CountIf(Range("U3:U" & finRow), "<365")




End Sub

 

[Fluff]

Have a look at Countifs, rather than countif

https://www.ablebits.com/office-addins-blog/2014/07/10/excel-countifs-multiple-criteria/

 

[MatthewLane412]

Re: Countifs

Have a look at Countifs, rather than countif

https://www.ablebits.com/office-addins-blog/2014/07/10/excel-countifs-multiple-criteria/

Thank you for the link. "Countifs" are exactly what I should have been doing. Unfortunately, I am still having trouble, trouble shooting this. When I use the code below, I come up with Zero. I'm still an amateur when I comes to VBA. Any insight you could give me would be much appreciated.

Thanks,
Matt

Sub IncomeStatement()
Dim I As Long
    finRow = Sheet1.Range("A10000").End(xlUp).Row
    
I = 3
Sheet3.Range("B8") = Application.WorksheetFunction.CountIfs(Sheets("Sheet1").Range("AH3:AH" & finRow), "<365", Range("I3:I" & finRow), "<>666")

End Sub

 

[Fluff]

Re: Countifs

Assuming that both ranges are on sheet1 try

Sub IncomeStatement()
Dim I As Long
    finRow = Sheet1.Range("A10000").End(xlUp).Row
    
I = 3
With Sheets("Sheet1")
    Sheet3.Range("B8") = Application.WorksheetFunction.CountIfs(.Range("AH3:AH" & finRow), "<365", .Range("I3:I" & finRow), "<>666")
End With
End Sub

AFAIK countifs will not work on multiple sheets.

 

[MatthewLane412]

Re: Countifs

Assuming that both ranges are on sheet1 try

Sub IncomeStatement()
Dim I As Long
    finRow = Sheet1.Range("A10000").End(xlUp).Row
    
I = 3
With Sheets("Sheet1")
    Sheet3.Range("B8") = Application.WorksheetFunction.CountIfs(.Range("AH3:AH" & finRow), "<365", .Range("I3:I" & finRow), "<>666")
End With
End Sub

AFAIK countifs will not work on multiple sheets.

This worked perfect once I remembered to use the "text to Columns" function. Thank you very much for your time.

Matt

 

[Fluff]

Re: Countifs

Glad to help & thanks for the feedback