Adapt file to show data for any given date

kronkito

New Member
Joined
Sep 22, 2017
Messages
2
Hi!
Let me apologize for the vague title but the one thing that i'm looking for I don't even know if it has a technical name -which limits my ability to google it-.

I have a file with 22.000 rows and these are the headers for each column:

SERIAL NUMBER LOCATION_MONITORING LEVEL EFFECTIVE DATE EXPIRATION DATE


It basically registers when an unit's monitoring level changes and it includes when starts and when it ends.

I'm trying to get a way to be able to determine in any given date how many units where on each monitoring level.

Any ideas on how to do this? Thanks in advance!! :)






7xnDy
JOdadIU


7xnDy
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Assuming I have your layout properly, you can use COUNTIFS to find your results:

ABCDEFGH
1Serial NumberLocation_Monitoing LevelEffective DateExpiration DateDateLevelsCount
2a111-Jan31-Jan15-Feb11
3a121-Feb28-Feb23
4a131-Mar31-Dec30
5b211-Jan31-Jan41
6c311-May31-May50
7b221-Feb1-Mar
8c321-Apr1-Aug
9b232-Mar1-Jul
10d411-Jan1-Oct
11d422-Oct31-Oct
12d431-NovDec-35
13e541-Jan1-Jul
14e652-Jul31-Dec
15f721-Jan1-Mar
16f732-Mar11-Sep
17

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet14

Worksheet Formulas
CellFormula
H2=COUNTIFS(B:B,G2,C:C,"<="&$F$2,D:D,">="&$F$2)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Ufff, I have just started to learn about PowerPivot so I was trying to do it using something close to it and it seems I forgot to try the easiest way.

Thank you very much! If anyone finds a fancier way to do it it'd be greatly appreciated too!

:)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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?

Disable AdBlock

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
Back
Top