Excel - Using COUNTIFS to count dates within a certain range across multiple sheets

1
votes

I am trying to "COUNT" the number of dates that fall between a certain date range over multiple sheets excluding the first 'Summary' sheet. Following other examples I have used:

=COUNTIFS('CWP-A1-Inst'!S7:S51,">=2015-09-13" _ 
         ,'CWP-A1-Inst'!S7:S51,"<=2015-09-19") _ 
+ COUNTIFS('CWP-A2-Inst'!V7:V51,">=2015-09-13" _ 
          ,'CWP-A2-Inst'!V7:V51,"<=2015-09-19") _ 
+ COUNTIFS('CWP-A3-Inst'!T7:T50,">=2015-09-13" _ 
          ,'CWP-A3-Inst'!T7:T50,"<=2015-09-19")

This method works but I have 28 sheets, 75 date ranges (each week starting with Sunday ending with Saturday for over a year) and 7 cost codes to fill this out for. It would take forever to change these all manually.

I have also tried the UDF suggested in another example - Excel - Using COUNTIF/COUNTIFS across multiple sheets/same column - but I am unable to figure out how to add the second set of criteria in the example as I am by no means a VB expert.

Thanks in advance for any recommendations.

2
vbaexcel

2 Answers

0
votes

It is easier to get summary information about data when it is in a good structure.

If you had one big long list you would benefit from being able to use pivot tables For examples see here

If you were to take you data and put it in one long list this would be a good start (if the existing tables have the same structure).

Harvey

0
votes

Try this mod to the posted UDF link.

Edit: code has been fixed so now it works. I also added the "<=" into the function, so all you need to provide is the input dates as they are and nothing else.

Function myCountIf(rng As Range, startDate as Variant, endDate as Variant) As Long
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        myCountIf = myCountIf + WorksheetFunction.CountIf(ws.Range(rng.Address), "<=" & endDate) - WorksheetFunction.CountIf(ws.Range(rng.Address), "<=" & startDate)
    Next ws
End Function

And for startDate and endDate enter your criteria eg =myCountIf(A:A,">=2015-09-13","<=2015-09-19").