How To SUMIF Excluding Weekends & Holidays

Summing data based on weekdays or weekends is easy, but what if you are required to SUMIF Excluding Weekends & Holidays?

There are 2 ways of doing this:

Using SUM, IF & WORKDAY FUNCTION

=SUM(IF ( WORKDAY(tbls[Sales Date]-1,1,Holidays) = tbls[Sales Date],tbls[Sales]))

How it works:

WORKDAY(tbls[Sales Date]-1,1,Holidays) ► Checks if the sales dates are working days taking into account weekends and holidays…if they are not, the WORKDAY function adjusts the date forward to the next working day.

WORKDAY(tbls[Sales Date]-1,1,Holidays) = tbls[Sales Date] ► if the returned date is the same as the initial sales date, this test returns TRUE else FALSE

For all TRUE results, IF function returns the sales values IF (WORKDAY(tbls[Sales Date]-1,1,Holidays) = tbls[Sales Date],tbls[Sales])

SUM function aggregates only these returned Sales Value

To SUM for Non-Workdays

=SUM(IF(WORKDAY(tbls[Sales Date]-1,1,Holidays)=tbls[Sales Date],0,tbls[Sales]))

Just ensure you return 0 for all TRUE values while you return Sales value for FALSE values

Using SUMPRODUCT & WORKDAY FUNCTION

How it Works:

WORKDAY(tbls[Sales Date]-1,1,Holidays) ► Checks if the sales dates are working days taking into account weekends and holidays…if they are not, the WORKDAY function adjusts the date forward to the next working day.

WORKDAY(tbls[Sales Date]-1,1,Holidays) = tbls[Sales Date] ► if the returned date is the same as the initial sales date, this test returns TRUE else FALSE

Since excel converts TRUE =1 and FALSE =0 then multiply this array with sales values to get sales values for workdays only.

SUMPRODUCT sums the final sales values.

DOWNLOAD WORKBOOK

More Reads

https://exceljet.net/formula/date-is-workday

HOW TO SUMIFS EXACT MATCH IN EXCEL