COUNTIFS Date Range problem

**dtrimble** · 12-02-2012, 12:57 PM

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:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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** · 12-02-2012, 01:06 PM

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)

**Ace_XL** · 12-02-2012, 01:06 PM

try

Please Login or Register  to view this content.

**Teethless mama** · 12-02-2012, 01:08 PM

Post your workbook

**dtrimble** · 12-02-2012, 01:09 PM

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

**dtrimble** · 12-02-2012, 01:10 PM

Please Login or Register  to view this content.

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

What are ampersand & characters doing in this formula?

**daddylonglegs** · 12-02-2012, 01:23 PM

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** · 12-02-2012, 01:32 PM

Originally Posted by 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()

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

Dan