View Full Version : Filter and sum the column values

06-17-2019, 12:48 AM
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

06-17-2019, 04:14 AM
Two different ways in the attached in cells B3 and B6.

06-19-2019, 02:34 PM
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.

06-19-2019, 03:45 PM
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")
'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

06-20-2019, 09:56 AM
Hi p45cal,

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