# counting dates in multiple ranges

#### Searshc

##### New Member
I have a sheet with multiple employees training dates setup as a numbered range (ie, fiscal year/fiscal week like 201604). Because the training is not always the same length of time, I need to be able to show how many employees are in training for each fiscal week. However, because the data is stored per employee, I have multiple training start/end dates, and every time I try using 'countifs' it comes back with zero, even though I know I have 79 people in training this week.

Is there a way to countif for each employee since the date ranges are there for each person?

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
Hello, can you post a sample of your data and the expected result? Otherwise, with the limited information you have provided, it would be very difficult to give you a solution.

Yes, I hope this helps (not sure how to format this). Basically I have a start and end date, by fiscal week with the employee names. In another column, I have a list of the fiscal dates and I need a count for the number of employees in training within that time frame (the ones in A and B). Countifs kept coming up with zero. I can look up what date they start, but it won't keep track of the number of weeks they are actively in training, and then I can count the end date. But I need to show week by week how many people are in training.

 Start Date End Date Name 201509 201517 Smith 201511 201519 Joe 201509 201517 Stan Fiscal Week Count 201511 201519 Lee 201508 4 201506 201514 Robert 201509 5 201506 201514 James 201510 Etc 201507 201515 Chris 201511 Etc 201501 201509 Leslie 201450 201506 Zack 201445 201501 John 201447 201503 Steve 201446 201502 Ryan 201436 201444 Mike 201438 201446 Bill 201434 201442 Henry 201433 201441 Kate 201431 201439 Jenny 201431 201439 Doc Oc 201428 201436 Frank

<colgroup><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

Hello try this - assumes data with headings is at A1 to C20, and criteria with headings starts at D1, formula starts at E2 for 201508 at D2:

=COUNTIFS(\$A\$2:\$A\$20,"<="&D2,\$B\$2:\$B\$20,">="&D2)

Replies
7
Views
232
Replies
1
Views
211
Replies
5
Views
672
Replies
1
Views
106
Replies
8
Views
910

1,219,671
Messages
6,149,613
Members
450,904
Latest member
Gracifer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back