1

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.

Improve this question

2 Answers 2

Reset to default
0

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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

Improve this answer

7 Comments

I have tried this and get a #VALUE? returned. All of the manually inputted dates are in column O on all 28 sheets. The dates I am referencing are on row 6 of the summary sheet, which are all Sundays. I tried entering my criteria as: =mycountif(O:O, ">=C6", "<D6") where C6 is 2015-09-13 and D6 is 2015-09-20.
When excel sees ">=C6" it reads the whole thing as a string. Instead, try ">=" & C6. By doing that, it will concatenate the value from C6 with the ">=" operator.
I'm still getting a #VALUE! after trying this. I am going to pick up a VB book to learn how to do this a little better. Thanks for your help with this.
I think I figured it out. Instead of doing StartDate as >= date, you should use <=. Ok, I apologize for this being confusing, but the function is set to be Countif(Range, <=EndDate) - Countif(Range, <=StartDate), so that would give the range in between those two dates.
I figured it out. Apparently doing that trick where I used the & _ to extend the line, excel didn't like that in this formula. Removed that and it works. I tested it on a spreadsheet I just made.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.