COUNTIFS Date Range problem

[dtrimble]

Good morning,

I have a spreadsheet where each row is a person. The columns reflect the organization they work for, the date they arrived to our location, and the date they're planning to depart.

I am trying to calculate a total number of people based on what organization they're assigned to, and when they're departing--so we can plan schedules accordingly.

I believe this is best done as a a multi-part COUNTIFS. The first part checks the organization they're with (so I can have a separate total for each org). And the 2nd part calculates based on when they're leaving.

The easy part was calculating those leaving TODAY() and tomorrow -- TODAY()+1. I used the following and it worked as expected:

=COUNTIFS('Master Check-In'!$D$3:$D$80,"=ORG1",'Master Check-In'!$K$3:$K$80,TODAY())

=COUNTIFS('Master Check-In'!$D$3:$D$80,"=ORG1",'Master Check-In'!$K$3:$K$80,(TODAY()+1))

But for my totals of who's leaving anytime in the next week (7 days), it's not working as expected. What I have is this formula:

=COUNTIFS('Master Check-In'!$D$3:$D$80,"=ORG1",'Master Check-In'!$K$3:$K$80,(TODAY()+7))

As written, I know it's only calculating those leaving exactly 7 days from today -- not what I want, which is anybody leaving today, tomorrow, or any time between today and 1 week from now. But my relatively moderate Excel skills aren't figuring out successfully total up within a range.


This is driving me nuts and I have to get it fixed this afternoon. Any help would surely be appreciated.


Dan

[daddylonglegs]

You need a start and end date, e.g.

=COUNTIFS('Master Check-In'!$D$3:$D$80,"ORG1",'Master Check-In'!$K$3:$K$80,">="&TODAY(),'Master Check-In'!$K$3:$K$80,"<"&TODAY()+7)

[dtrimble]

=COUNTIFS('Master Check-In'!$D$3:$D$80,"ORG1",'Master Check-In'!$K$3:$K$80,">="&TODAY(),'Master Check-In'!$K$3:$K$80,"<"&TODAY()+7)

This is what I was trying to do, but wasn't quite getting there.

What are ampersand & characters doing in this formula?

[Ace_XL]

try

=COUNTIFS('Master check-in'!$D$3:$D$80,"=ORG1",'Master check-in'!$K$3:$K$80,">="&TODAY(),'Master check-in'!$K$3:$K$80,"<="&TODAY()+7)

[Teethless mama]

Post your workbook

[dtrimble]

I can't. It contains confidential information and too much of it to scrub.

[daddylonglegs]

In COUNTIFS, COUNTIF, SUMIF etc. the criteria need to be text strings so "greater than 2" will be ">2" but if the 2 is a cell value or the result of a formula then you need the & to "concatenate" that value to the operator, e.g. if you had today's date in Z2 that would be ">="&Z2 and it works the same if it's a function like TODAY()

[dtrimble]

In COUNTIFS, COUNTIF, SUMIF etc. the criteria need to be text strings so "greater than 2" will be ">2" but if the 2 is a cell value or the result of a formula then you need the & to "concatenate" that value to the operator, e.g. if you had today's date in Z2 that would be ">="&Z2 and it works the same if it's a function like TODAY()

Thank you so very much; this is quite helpful. I appreciate your time.

Dan