Display the count on each day of the month based on start and stop date and match criteria

dgroff · Jan 22, 2021

I have a list of people that have individual start dates and end dates as well as an "X" criteria.

I want to be able to show the number of people for each day of the month that match the criteria "MA" and falls between their start and stop dates.

1/1

1/2

1/3

1/4

1/5

1/6

1/7

1/8

1/9

1/10

1/11

1/12

1/13

1/14

1/15

1/16

1/17

1/18

1/19

1/20

1/21

1/22

1/23

1/24

1/25

1/26

1/27

1/28

1/29

1/30

1/31

Person

Start

End

X

0

Person 1

1/10/2021

1/20/2021

MA

Person 2

1/10/2021

1/20/2021

MA

Person 3

1/12/2021

1/22/2021

MA

Person 4

1/12/2021

1/22/2021

MA

Person 5

1/12/2021

1/22/2021

MA

Person 6

1/13/2021

1/23/2021

MA

Person 7

1/13/2021

1/23/2021

MA

Person 8

1/13/2021

1/23/2021

AA

Person 9

1/13/2021

1/23/2021

MA

Person 10

1/13/2021

1/23/2021

MA

Person 11

1/13/2021

1/23/2021

AA

Person 12

1/13/2021

1/23/2021

MA

Person 13

1/13/2021

1/23/2021

MA

Person 14

1/13/2021

1/23/2021

MA

Person 15

1/13/2021

1/23/2021

AA

Person 16

1/13/2021

1/23/2021

MA

Person 17

1/13/2021

1/23/2021

MA

Person 18

1/13/2021

1/23/2021

AA

Person 19

1/13/2021

1/23/2021

AA

Person 20

1/13/2021

1/23/2021

MA

Person 21

1/13/2021

1/23/2021

MA

Person 22

1/13/2021

1/23/2021

MA

Person 23

1/13/2021

1/23/2021

AA

Person 24

1/13/2021

1/23/2021

MA

Person 25

1/13/2021

1/23/2021

MA

Person 26

1/13/2021

1/23/2021

MA

Person 27

1/13/2021

1/23/2021

MA

Person 28

1/13/2021

1/23/2021

MA

Person 29

1/13/2021

1/23/2021

AA

Person 30

1/13/2021

1/23/2021

MA

Person 31

1/13/2021

1/23/2021

MA

Person 32

1/13/2021

1/23/2021

MA

Person 33

1/13/2021

1/23/2021

MA

Person 34

1/13/2021

1/23/2021

MA

Person 35

1/13/2021

1/23/2021

MA

Person 36

1/13/2021

1/23/2021

MA

Person 37

1/14/2020

1/24/2020

MA

Person 38

1/14/2020

1/24/2020

MA

Person 39

1/16/2020

1/26/2020

MA

Person 40

1/16/2020

1/26/2020

MA

Person 41

1/16/2020

1/26/2020

MA

Person 42

1/16/2020

1/26/2020

MA

Person 43

1/16/2020

1/26/2020

MA

Person 44

1/16/2020

1/26/2020

MA

Person 45

1/16/2020

1/26/2020

MA

Person 46

1/16/2020

1/26/2020

MA

Person 47

1/16/2020

1/26/2020

AA

Person 48

1/16/2020

1/26/2020

MA

Person 49

1/16/2020

1/26/2020

AA

Person 50

1/16/2020

1/26/2020

AA

Person 51

1/16/2020

1/26/2020

MA

Person 52

1/16/2020

1/26/2020

MA

Person 53

1/16/2020

1/26/2020

AA

Person 54

1/16/2020

1/26/2020

MA

Person 55

1/16/2020

1/26/2020

MA

Person 56

1/16/2020

1/26/2020

AA

Person 57

1/16/2020

1/26/2020

MA

Person 58

1/16/2020

1/26/2020

MA

Person 59

1/16/2020

1/26/2020

MA

Person 60

1/16/2020

1/26/2020

MA

Person 61

1/16/2020

1/26/2020

MA

Person 62

1/16/2020

1/26/2020

MA

Person 63

1/16/2020

1/26/2020

MA

Person 64

1/16/2020

1/26/2020

MA

Person 65

1/16/2020

1/26/2020

MA

Person 66

1/16/2020

1/26/2020

MA

Person 67

1/17/2021

1/27/2021

MA

Fluff · Jan 22, 2021

How about

+Fluff v2.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

1

01-Jan

02-Jan

03-Jan

04-Jan

05-Jan

06-Jan

07-Jan

08-Jan

09-Jan

10-Jan

11-Jan

12-Jan

13-Jan

14-Jan

15-Jan

16-Jan

17-Jan

18-Jan

19-Jan

20-Jan

21-Jan

22-Jan

23-Jan

24-Jan

25-Jan

26-Jan

27-Jan

28-Jan

29-Jan

30-Jan

31-Jan

Person

Start

End

X

2

Person 1

10/01/2021

20/01/2021

MA

3

Person 2

10/01/2021

20/01/2021

MA

4

Person 3

12/01/2021

22/01/2021

MA

5

Person 4

12/01/2021

22/01/2021

MA

6

Person 5

12/01/2021

22/01/2021

MA

7

Person 6

13/01/2021

23/01/2021

MA

8

Person 7

13/01/2021

23/01/2021

MA

9

Person 8

13/01/2021

23/01/2021

AA

10

Person 9

13/01/2021

23/01/2021

MA

11

Person 10

13/01/2021

23/01/2021

MA

12

Person 11

13/01/2021

23/01/2021

AA

13

Person 12

13/01/2021

23/01/2021

MA

14

Person 13

13/01/2021

23/01/2021

MA

15

Person 14

13/01/2021

23/01/2021

MA

16

Person 15

13/01/2021

23/01/2021

AA

17

Person 16

13/01/2021

23/01/2021

MA

18

Person 17

13/01/2021

23/01/2021

MA

19

Person 18

13/01/2021

23/01/2021

AA

20

Person 19

13/01/2021

23/01/2021

AA

21

Person 20

13/01/2021

23/01/2021

MA

22

Person 21

13/01/2021

23/01/2021

MA

23

Person 22

13/01/2021

23/01/2021

MA

24

Person 23

13/01/2021

23/01/2021

AA

25

Person 24

13/01/2021

23/01/2021

MA

26

Person 25

13/01/2021

23/01/2021

MA

27

Person 26

13/01/2021

23/01/2021

MA

28

Person 27

13/01/2021

23/01/2021

MA

29

Person 28

13/01/2021

23/01/2021

MA

30

Person 29

13/01/2021

23/01/2021

AA

31

Person 30

13/01/2021

23/01/2021

MA

32

Person 31

13/01/2021

23/01/2021

MA

33

Person 32

13/01/2021

23/01/2021

MA

34

Person 33

13/01/2021

23/01/2021

MA

35

Person 34

13/01/2021

23/01/2021

MA

36

Person 35

13/01/2021

23/01/2021

MA

37

Person 36

13/01/2021

23/01/2021

MA

38

Person 37

14/01/2020

24/01/2020

MA

39

Person 38

14/01/2020

24/01/2020

MA

40

Person 39

16/01/2020

26/01/2020

MA

41

Person 40

16/01/2020

26/01/2020

MA

42

Person 41

16/01/2020

26/01/2020

MA

43

Person 42

16/01/2020

26/01/2020

MA

44

Person 43

16/01/2020

26/01/2020

MA

45

Person 44

16/01/2020

26/01/2020

MA

46

Person 45

16/01/2020

26/01/2020

MA

47

Person 46

16/01/2020

26/01/2020

MA

48

Person 47

16/01/2020

26/01/2020

AA

49

Person 48

16/01/2020

26/01/2020

MA

50

Person 49

16/01/2020

26/01/2020

AA

51

Person 50

16/01/2020

26/01/2020

AA

52

Person 51

16/01/2020

26/01/2020

MA

53

Person 52

16/01/2020

26/01/2020

MA

54

Person 53

16/01/2020

26/01/2020

AA

55

Person 54

16/01/2020

26/01/2020

MA

56

Person 55

16/01/2020

26/01/2020

MA

57

Person 56

16/01/2020

26/01/2020

AA

58

Person 57

16/01/2020

26/01/2020

MA

59

Person 58

16/01/2020

26/01/2020

MA

60

Person 59

16/01/2020

26/01/2020

MA

61

Person 60

16/01/2020

26/01/2020

MA

62

Person 61

16/01/2020

26/01/2020

MA

63

Person 62

16/01/2020

26/01/2020

MA

64

Person 63

16/01/2020

26/01/2020

MA

65

Person 64

16/01/2020

26/01/2020

MA

66

Person 65

16/01/2020

26/01/2020

MA

67

Person 66

16/01/2020

26/01/2020

MA

68

Person 67

17/01/2021

27/01/2021

MA

Ref

Cell Formulas
Range	Formula
A2:AE2	A2	=COUNTIFS($AJ:$AJ,"MA",$AH:$AH,"<="&A1,$AI:$AI,">="&A1)

maabadi · Jan 22, 2021

Please Upload Example file & Desired results with XL2BB ADDIN(Preferable) or upload at free hosting Site e.g. www.dropbox.com , GoogleDrive or OneDrive & insert Link here.

MrTomasz · Jan 22, 2021

try countifs:
>=day on column start date
<=day on column end date
MA on column X

----
too late

dgroff · Jan 22, 2021

Fluff said:
How about

+Fluff v2.xlsm
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ
1 01-Jan 02-Jan 03-Jan 04-Jan 05-Jan 06-Jan 07-Jan 08-Jan 09-Jan 10-Jan 11-Jan 12-Jan 13-Jan 14-Jan 15-Jan 16-Jan 17-Jan 18-Jan 19-Jan 20-Jan 21-Jan 22-Jan 23-Jan 24-Jan 25-Jan 26-Jan 27-Jan 28-Jan 29-Jan 30-Jan 31-Jan Person Start End X
2 0 0 0 0 0 0 0 0 0 2 2 5 29 29 29 29 30 30 30 30 28 28 25 1 1 1 1 0 0 0 0 Person 1 10/01/2021 20/01/2021 MA
3 Person 2 10/01/2021 20/01/2021 MA
4 Person 3 12/01/2021 22/01/2021 MA
5 Person 4 12/01/2021 22/01/2021 MA
6 Person 5 12/01/2021 22/01/2021 MA
7 Person 6 13/01/2021 23/01/2021 MA
8 Person 7 13/01/2021 23/01/2021 MA
9 Person 8 13/01/2021 23/01/2021 AA
10 Person 9 13/01/2021 23/01/2021 MA
11 Person 10 13/01/2021 23/01/2021 MA
12 Person 11 13/01/2021 23/01/2021 AA
13 Person 12 13/01/2021 23/01/2021 MA
14 Person 13 13/01/2021 23/01/2021 MA
15 Person 14 13/01/2021 23/01/2021 MA
16 Person 15 13/01/2021 23/01/2021 AA
17 Person 16 13/01/2021 23/01/2021 MA
18 Person 17 13/01/2021 23/01/2021 MA
19 Person 18 13/01/2021 23/01/2021 AA
20 Person 19 13/01/2021 23/01/2021 AA
21 Person 20 13/01/2021 23/01/2021 MA
22 Person 21 13/01/2021 23/01/2021 MA
23 Person 22 13/01/2021 23/01/2021 MA
24 Person 23 13/01/2021 23/01/2021 AA
25 Person 24 13/01/2021 23/01/2021 MA
26 Person 25 13/01/2021 23/01/2021 MA
27 Person 26 13/01/2021 23/01/2021 MA
28 Person 27 13/01/2021 23/01/2021 MA
29 Person 28 13/01/2021 23/01/2021 MA
30 Person 29 13/01/2021 23/01/2021 AA
31 Person 30 13/01/2021 23/01/2021 MA
32 Person 31 13/01/2021 23/01/2021 MA
33 Person 32 13/01/2021 23/01/2021 MA
34 Person 33 13/01/2021 23/01/2021 MA
35 Person 34 13/01/2021 23/01/2021 MA
36 Person 35 13/01/2021 23/01/2021 MA
37 Person 36 13/01/2021 23/01/2021 MA
38 Person 37 14/01/2020 24/01/2020 MA
39 Person 38 14/01/2020 24/01/2020 MA
40 Person 39 16/01/2020 26/01/2020 MA
41 Person 40 16/01/2020 26/01/2020 MA
42 Person 41 16/01/2020 26/01/2020 MA
43 Person 42 16/01/2020 26/01/2020 MA
44 Person 43 16/01/2020 26/01/2020 MA
45 Person 44 16/01/2020 26/01/2020 MA
46 Person 45 16/01/2020 26/01/2020 MA
47 Person 46 16/01/2020 26/01/2020 MA
48 Person 47 16/01/2020 26/01/2020 AA
49 Person 48 16/01/2020 26/01/2020 MA
50 Person 49 16/01/2020 26/01/2020 AA
51 Person 50 16/01/2020 26/01/2020 AA
52 Person 51 16/01/2020 26/01/2020 MA
53 Person 52 16/01/2020 26/01/2020 MA
54 Person 53 16/01/2020 26/01/2020 AA
55 Person 54 16/01/2020 26/01/2020 MA
56 Person 55 16/01/2020 26/01/2020 MA
57 Person 56 16/01/2020 26/01/2020 AA
58 Person 57 16/01/2020 26/01/2020 MA
59 Person 58 16/01/2020 26/01/2020 MA
60 Person 59 16/01/2020 26/01/2020 MA
61 Person 60 16/01/2020 26/01/2020 MA
62 Person 61 16/01/2020 26/01/2020 MA
63 Person 62 16/01/2020 26/01/2020 MA
64 Person 63 16/01/2020 26/01/2020 MA
65 Person 64 16/01/2020 26/01/2020 MA
66 Person 65 16/01/2020 26/01/2020 MA
67 Person 66 16/01/2020 26/01/2020 MA
68 Person 67 17/01/2021 27/01/2021 MA
Ref
Cell Formulas
Range Formula
A2:AE2 A2 =COUNTIFS($AJ:$AJ,"MA",$AH:$AH,"<="&A1,$AI:$AI,">="&A1)

This is Perfect! Thank you!

Fluff · Jan 22, 2021

Glad we could help & thanks for the feedback.

Display the count on each day of the month based on start and stop date and match criteria

dgroff

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

maabadi

Well-known Member

MrTomasz

Board Regular

dgroff

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Display the count on each day of the month based on start and stop date and match criteria

dgroff

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

maabadi

Well-known Member

MrTomasz

Board Regular

dgroff

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock