Filter and sum the column values

Dear I have a consolidated history.xlsx Excel file. In thison consolidated histy sheet I need to sum the column J (Manday) values after deselecting (holiday & leave) on column K ( Nature of visit). The filter condition is it has to de-select only holiday & leave. And the sum value should be display in sheet2 B3 cell.

I am attaching my excel file here.


Pls give me the program for the above condition

Two different ways in the attached in cells B3 and B6.

Hi p45cal

Thanks for your reply. The formula given by you is working as per my requirement.

I have one more doubt.

Is it possible to add the same formula on VBA code.

Code:

---

Sub blah()
Set CHData = Sheets("Consolidated History").Range("A1").CurrentRegion
Set CHData = Intersect(CHData, CHData.Offset(1)) 'just the databody, not the headers.

Sheets("Sheet2").Range("B3").Value =Application.WorksheetFunction.SumIfs(CHData.Columns("J"), CHData.Columns("K"), "<>Leave", CHData.Columns("K"), "<>Holiday")
'longhand:
'Z = Application.WorksheetFunction.SumIfs(CHData.Columns("J"), CHData.Columns("K"), "<>Leave", CHData.Columns("K"), "<>Holiday")
'MsgBox Z
'Sheets("Sheet2").Range("B3").Value = Z
End Sub

---

Hi p45cal,

Thanks for your code. It is working great as per my conditions.