countifs excluding holidays

Try this...

=SUMPRODUCT((Sheet1!D:D=A4)*(Sheet1!G:G<WORKDAY(TODAY(),-10,HolidayList))*(Sheet1!G:G>WORKDAY(TODAY(),-21,HolidayList)))

Select allOpen in new window

And this...

=Sumproduct((Sheet1!D:D=A3)*(Sheet1!G:G<WORKDAY(TODAY(),-20,HolidayList)))

Select allOpen in new window

If this doesn't give you desired results then please post your worksheet..

Saurabh...

try Saurabh   solution and if that fails then let me know, so that i can provide you with solution

By adding a holiday list in to the WORKDAY function you are excluding holidays only in the calculation of the cutoff dates, not then within the COUNTIFS formula, if you want to do both try this variation on Saurabh's suggestion

=SUMPRODUCT((Sheet1!D:D=A4)*(Sheet1!G:G<WORKDAY(TODAY(),-10,HolidayList))*(Sheet1!G:G>WORKDAY(TODAY(),-21,HolidayList))*ISNA(MATCH(Sheet1!G:G),HolidayList,0)))

and

=SUMPRODUCT((Sheet1!D:D=A3)*(Sheet1!G:G<WORKDAY(TODAY(),-20,HolidayList)))*ISNA(MATCH(Sheet1!G:G),HolidayList,0)))

regards, barry

Hi Saurabh,

That didn't seem to work. Maybe I still have syntax issues...


Sub Count()

Dim HolidaysList()

HolidaysList = Array("1/1/2014", "1/15/2014", "2/19/2014", "5/28/2014", "7/4/2014", "9/3/2014", "10/8/2014", "11/12/2014", _
"11/22/2014", "12/25/2014", "1/1/2015", "1/2/2015", "2/16/2015", "5/25/2015", "7/6/2015", "9/7/2015", _
"11/26/2015", "11/27/2015", "12/24/2015", "12/25/2015")

'Range("G4").Formula = "=COUNTIFS(Sheet1!D:D,A4,Sheet1!G:G,""<""&WORKDAY(TODAY(),-10),Sheet1!G:G,"">""&WORKDAY(TODAY(),-21))"
Range("G4").Formula = "=SUMPRODUCT((Sheet1!D:D""=""A4)""*""(Sheet1!G:G""<""WORKDAY(TODAY(),-10,HolidayList))""*""(Sheet1!G:G"">""WORKDAY(TODAY(),-21,HolidayList)))"

'Range("H4").Formula = "=COUNTIFS(Sheet1!D:D,A4,Sheet1!G:G,""<""&WORKDAY(TODAY(),-20))"
Range("H4").Formula = "=SUMPRODUCT((Sheet1!D:D""=""A4)""*""(Sheet1!G:G""<""WORKDAY(TODAY(),-20,HolidayList)))"

End Sub

I'll start working on a sheet I can post.

Thanks,

swjtx99

swjtx99 ,

This won't work because you are applying Holidaylist in VB and then applying the same in the formula and it won't pull the data that you are looking for.

Create a tab and in their create a name range of holidaylist by applying this dates their and then see it will give you desired results..

Saurabh...

How about using NETWORKDAYS function? This tells you how many whole days excluding Saturday, Sunday and holiday list.

With a slightly different approach, you can calculate the number of days in a helper column and then count the occurences of values greater than 20. It might be possible to combine into an array formula to avoid the helper column with number of days.

Thanks
Rob H

Hi,

This "looks" right but still no cigar. Example sheet attached.

Sub Count()

Worksheets.Add.Name = "Holidays"
   
    Sheets("Holidays").Move after:=Sheets("Sheet2")
    Sheets("Holidays").Select

Range("A1").Select
    ActiveCell.FormulaR1C1 = "1/1/2015"
Range("A2").Select
    ActiveCell.FormulaR1C1 = "1/2/2015"
Range("A3").Select
    ActiveCell.FormulaR1C1 = "2/16/2015"
Range("A4").Select
    ActiveCell.FormulaR1C1 = "5/25/2015"
Range("A5").Select
    ActiveCell.FormulaR1C1 = "7/6/2015"
Range("A6").Select
    ActiveCell.FormulaR1C1 = "9/7/2015"
Range("A7").Select
    ActiveCell.FormulaR1C1 = "11/26/2015"
Range("A8").Select
    ActiveCell.FormulaR1C1 = "11/27/2015"
Range("A9").Select
    ActiveCell.FormulaR1C1 = "12/24/2015"
Range("A10").Select
    ActiveCell.FormulaR1C1 = "12/25/2015"

    Columns("A:A").Select
    ActiveWorkbook.Names.Add Name:="HolidayList", RefersToR1C1:="=Holidays!C1"
    Range("A1").Select

ActiveWorkbook.Sheets("Sheet2").Activate


 'Range("G4").Formula = "=COUNTIFS(Sheet1!D:D,A4,Sheet1!G:G,""<""&WORKDAY(TODAY(),-10),Sheet1!G:G,"">""&WORKDAY(TODAY(),-21))"
 Range("G4").Formula = "=SUMPRODUCT((Sheet1!D:D""=""A4)""*""(Sheet1!G:G""<""WORKDAY(TODAY(),-10,HolidayList))""*""(Sheet1!G:G"">""WORKDAY(TODAY(),-21,HolidayList)))"

 'Range("H4").Formula = "=COUNTIFS(Sheet1!D:D,A4,Sheet1!G:G,""<""&WORKDAY(TODAY(),-20))"
 Range("H4").Formula = "=SUMPRODUCT((Sheet1!D:D""=""A4)""*""(Sheet1!G:G""<""WORKDAY(TODAY(),-20,HolidayList)))"

 End Sub

Thanks,

swjtx99

Your file is still missing..

Sorry. 2nd try.
Test.xls

This one is a better example.
Test1.xls

On sheet 1 in column H add this formula against all dates in column G, starting in H2:

=NETWORKDAYS(G2,TODAY(),Sheet2!$A$7:$A$18)

Range Sheet2!A7:A18 contains Holiday dates.

Then formulas on sheet 2:

H3  for Count 11 to 20: =COUNTIFS(Sheet1!$D:$D,$A$4,Sheet1!$H:$H,">"&10)-H4
H4 for count >20: =COUNTIFS(Sheet1!$D:$D,$A$4,Sheet1!$H:$H,">"&20)

Note formula in H3 counts all entries >10 but then deducts the result of H4 where count is >20

Thanks
Rob H

Hi Rob H,

I can't add formulas to Sheet1. I can't do anything to Sheet1. All I want to do is when Sheet1 column D matches Sheet2 A4, how many of those matches are >20 days old, and between 10 and 20 days old, excluding holidays. In my example above, I was doing this by inserting a Countifs formula and that worked except it wasn't excluding holidays. Perhaps I can go back to that now since I've added a "holidays" tab with a named range? ...although I don't see where "Holidays" is part of the criteria in the Countifs formula......

swjtx99

Holidays won't be part of the COUNTIFS formula. Would any of the dates in the list be a holiday date anyway??

I thought you were comparing the entries in the list of dates to today to get a number of working days at which point you are ignoring holidays and then counting the number of entries in the two groups.

I wonder if can be done with DCOUNT.

I will take a look.

UPDATE:

DCOUNT can be used to count number of entries between a pair of dates, eg today less 10 and today less 20, but does not allow for working days.

Actually, maybe it can.

Just found a function in Excel 2010 called WORDAY.INTL where you can specify a start date, a number of working days, weekends and holidays. The result is a date, in other words with the correct parameters, the date at which entries in the list become more than 10 (or 20) working days old.

You talk about "excluding holidays", but as I said in my first post, it depends where you want to do that. If you want to exclude holidays when you calculate the dates between which you need your count then you need to exclude the holidays by using HolidayList in the WORKDAY function (as you already did), but that only defines the dates that you want to count between, it doesn't explicitly exclude holidays from that count, so you also need another criteria to exclude the dates - it's difficult to exclude a range of dates in COUNTIFS so that's easier to do with SUMPRODUCT, hence my original suggestion using this formula

=SUMPRODUCT((Sheet1!D:D=A4)*(Sheet1!G:G<WORKDAY(TODAY(),-10,HolidayList))*(Sheet1!G:G>WORKDAY(TODAY(),-21,HolidayList))*ISNA(MATCH(Sheet1!G:G),HolidayList,0)))

That uses the final part (ISNA/MATCH) to exclude the holidays from the count.

This assumes that HolidayList is a named range in your workbook

regards, barry

Hi Saurabh,

Your solution seemed to work but I found an issue. I used the formula to expand the breakout but it's counting the items with a holiday date twice. In the attached on sheet1 I have tagged everything as to where it should be counted on sheet 2 and it looks like the 2 items with a holiday are being counted in both the "3 to 5" category and the "6 to 10" category which adds 2 to the total 62 when it should only be 60.

Hi Rob H, haven't had the chance to try your suggestions yet.

Regards,

swjtx99
Test2.xls

Hi Barry,

Thanks for your suggestion. I'll try that as well as soon as I get a chance.

Thanks,

swjtx99

Please confirm - when calculating the number of days between the date in the list and today you want number of working days, so excluding weekend and holiday. Are you also then excluding the entries in the list where the date is actually a holiday?

Calculation of age = number of WORKING days prior to today, working day excludes holiday
Count of entries where age is greater than 10, exclude entries where date in list is a holiday.

Hi Rob,

It does get complicated doesn't it?

Count of entries where age is greater than X working days (or between X and X working days), not counting the holiday as a working day.

In my example, there were items that had a date on sheet1 of the 25th (a holiday). for the "3 to 5" working days category, the dates would be 26-28 May which is 3, 4 , and 5 working days before today. Those with a date of the 25th should be counted in the "6 to 10" category because while the 25th wasn't a workday, the date is beyond the 3, 4, and 5 range.

If you think of the date on Sheet1 as an "expiration date" that might make it more clear. Since the two with the 25th expired on the 25th, that was 1 workday  before items in the 3 to 5 category so would have to be included in the 6 to 10 category, even though the 6th workday was the 22nd.

Whether they are counted in one category or the other, they shouldn't be counted in both.

Hope that didn't make it more unclear.

Thanks,

swjtx99

Hi Barry,

I tried your formula and I'm getting too few arguments error.

Cursor stops here where I inserted 3 spaces.

=SUMPRODUCT((Sheet1!D:D=A4)*(Sheet1!G:G<WORKDAY(TODAY(),-10,HolidayList))*(Sheet1!G:G>WORKDAY(TODAY(),-21,HolidayList))*ISNA(MATCH(Sheet1!G:G    ),HolidayList,0)))

Thanks,,

swjtx99

Hi Barry, Rob and Saurabh,

I am going to close this question and divide the points equally as you each provided a workable solution for the problem I described and I sincerely appreciate your time and assistance.

It appears my problem has morphed into how to count how many workdays from a date is an item that is either a holiday or weekend.

It would have to be code that first determines if the date is a holiday or weekend, then adds it to the count for the next earliest workday.

For example, 25 May is today-7 workdays, except it was a holiday so an item would have to be counted as expired the prior workday which was the 22nd. The 24th was a Sunday so would also need to be added to the count for the 22nd (next prior workday).

Any ideas are appreciated but I don't think this can be done in an excel formula, maybe VBA code.

Regards,

swjtx99

I personally haven't known anyone knowing more than Barry when it comes to formulas related to dates in excel


Barry, I have been following your posts, I am impressed with the solutions on your date formulas

Hi Professorjimjam,

I tried Barry's and it works as do the others but the excel functions are not treating holidays and weekends the way I needed. I didn't discover the issue until I started applying the solutions to a list of dates. I spent a lot of hours tweaking the formulas but could not get consistent results. Still, I might have missed something but at this point I thought it best to move on, do something different and hope it was fair to split the points equally.

swjtx99

Hi Swjtx99

i just shared my opinion about Barry's knowledge, it wasn't anything to do with this thread or the points.

i am sorry to hear that you did not get what you were looking for.

Hello ProfessorJimJam,

Thanks for your comments, much appreciated!

Hello swjtx99,

I understand you've closed the question but I'm happy to work with you to get a solution if you wish. I don't do VBA so if you want VBA I'm out.....but I suspect this can be done with a formula, I'm just not sure I understand exactly what you want to do.

If you want to continue just post back with a small example, preferably, with required results

regards, barry