countifs excluding holidays

Hi,

I have a couple of formulas that work fine except they are not accounting for holidays.

The first formula looks at the dates in Column G (where Col D on another sheet matches what is in A4) and counts any that are between -10 and -21 days old
This Second formula looks at the dates in column G (where Col D on another sheet matches what is in A3) and counts any that are more than 20 workdays older than today.

=COUNTIFS(Sheet1!D:D,A4,Sheet1!G:G,"<"&WORKDAY(TODAY(),-10),Sheet1!G:G,">"&WORKDAY(TODAY(),-21))
=COUNTIFS(Sheet1!D:D,A3,Sheet1!G:G,"<"&WORKDAY(TODAY(),-20))"

I need them to also exclude holidays.
I tried using something from another Expert provided as part of a previous question but I don't have the syntax right (or more). What I tried to do was dim an array of holidays and use that in the formula:

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")

=COUNTIFS(Sheet1!D:D,A4,Sheet1!G:G,"<"&WORKDAY(TODAY(),-10,HolidayList),Sheet1!G:G,">"&WORKDAY(TODAY(),-21,HolidayList))
=COUNTIFS(Sheet1!D:D,A3,Sheet1!G:G,"<"&WORKDAY(TODAY(),-20,HolidayList))

This didn't work.

Thanks in advance,

swjtx99
swjtx99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Try this...

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

Open in new window


And this...

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

Open in new window


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

Saurabh...
0
ProfessorJimJamCommented:
try Saurabh   solution and if that fails then let me know, so that i can provide you with solution
0
barry houdiniCommented:
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
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

swjtx99Author Commented:
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
0
Saurabh Singh TeotiaCommented:
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...
0
Rob HensonFinance AnalystCommented:
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
0
swjtx99Author Commented:
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
0
Saurabh Singh TeotiaCommented:
Your file is still missing..
0
swjtx99Author Commented:
Sorry. 2nd try.
Test.xls
0
swjtx99Author Commented:
This one is a better example.
Test1.xls
0
Rob HensonFinance AnalystCommented:
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
0
swjtx99Author Commented:
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
0
Saurabh Singh TeotiaCommented:
Swjtx99,

This code works for me perfectly and rather taking the entire column i determine the lastrow as well basis of your example basis of D Column which gives me desired results...

Sub Count()
    Dim lrow As Long
    lrow = Sheets("Sheet1").Cells(Cells.Rows.Count, "d").End(xlUp).Row

    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!D2:D" & lrow & "=A4)*(Sheet1!G2:G" & lrow & "<WORKDAY(TODAY(),-10,HolidayList))*(Sheet1!G2:G" & lrow & ">WORKDAY(TODAY(),-21,HolidayList)))"

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

End Sub

Open in new window


Saurabh...
0
Rob HensonFinance AnalystCommented:
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.
0
Rob HensonFinance AnalystCommented:
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.
0
Rob HensonFinance AnalystCommented:
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.
0
Rob HensonFinance AnalystCommented:
See attached.

Populate green area with holiday dates.

Thanks
Rob H
Test1.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
barry houdiniCommented:
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
0
swjtx99Author Commented:
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
0
swjtx99Author Commented:
Hi Barry,

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

Thanks,

swjtx99
0
Rob HensonFinance AnalystCommented:
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.
0
swjtx99Author Commented:
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
0
swjtx99Author Commented:
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
0
barry houdiniCommented:
Sorry, there's an extra parenthesis that shouldn't be there - should be like this:

=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)))

Also I was a little lazy using whole columns - that's OK with COUNTIFS but with SUMPRODUCT it can slow down the formula considerably, better to use ranges restricted to the extent of your data, or a little more to allow for expansion

regards, barry
0
swjtx99Author Commented:
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
0
ProfessorJimJamCommented:
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
0
swjtx99Author Commented:
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
0
ProfessorJimJamCommented:
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.
0
barry houdiniCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.