I am using Countifs to calculate the number of meetings in a given month. I started with the following formula but have tried Indirect, Concatenate, Index and Match without success. =COUNTIFS(owner,$A$1,meetdate,">"&B3&B4,meetdate,"<6/1/2021"). B3 and B4 are variables that will trigger the named range, but not working. Cell B5 has the correct answer for May (27). But the above formula in B6 incorrectly yields 0 (zero). Spreadsheet is attached. I'm sure there are better ways to go about this. Any guidance appreciate. Mini-sheet below. Thanks for your attention.
Example 1-10-21 V2.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Philby | ||||||||
2 | |||||||||
3 | Criteria | First | |||||||
4 | May21 | ||||||||
5 | # Meetings (Original) | 27 | |||||||
6 | # Meetings (Problem) | 0 | |||||||
7 | |||||||||
8 | Criteria Table | Data | |||||||
9 | Month | First | Last | Owner | Deal Stage | MeetDate | |||
10 | May | 4/30/2021 | 6/1/2021 | Philby | Meeting Delivered | 11/29/2021 | |||
11 | June | 5/31/2021 | 7/1/2021 | Philby | Demo Delivered | 11/24/2021 | |||
12 | July | 6/30/2021 | 8/1/2021 | Philby | Closed Lost | 9/2/2021 | |||
13 | August | 7/31/2021 | 9/1/2021 | Philby | Closed Lost | 7/22/2021 | |||
14 | September | 8/31/2021 | 10/1/2021 | Philby | Closed Lost | 7/21/2021 | |||
15 | October | 9/30/2021 | 11/1/2021 | Philby | Closed Lost | 7/6/2021 | |||
16 | November | 10/31/2021 | 12/1/2021 | Philby | Closed Won | 5/14/2021 | |||
17 | December | 11/30/2021 | 1/1/2022 | Philby | Closed Lost | 5/3/2021 | |||
18 | Philby | Closed Lost | 5/18/2021 | ||||||
19 | Philby | Closed Won | 10/21/2021 | ||||||
20 | Philby | Closed Lost | 5/3/2021 | ||||||
21 | Philby | Discovery | 10/22/2021 | ||||||
22 | Philby | Closed Lost | 6/15/2021 | ||||||
23 | Philby | Closed Won | 8/3/2021 | ||||||
24 | Philby | Closed Lost | 9/21/2021 | ||||||
25 | Philby | Closed Lost | 10/29/2021 | ||||||
26 | Philby | Closed Lost | 7/26/2021 | ||||||
27 | Philby | Closed Lost | 7/9/2021 | ||||||
28 | Philby | Closed Lost | 5/14/2021 | ||||||
29 | Philby | Closed Lost | 7/27/2021 | ||||||
30 | Philby | Closed Lost | 6/28/2021 | ||||||
31 | Philby | Closed Lost | 9/21/2021 | ||||||
32 | Philby | Closed Lost | 8/11/2021 | ||||||
33 | Philby | Closed Lost | 11/2/2021 | ||||||
34 | Philby | Closed Lost | 9/29/2021 | ||||||
35 | Philby | Discovery | 9/15/2021 | ||||||
36 | Philby | Closed Lost | 5/24/2021 | ||||||
37 | Philby | Closed Lost | 5/20/2021 | ||||||
38 | Philby | Closed Won | 11/10/2021 | ||||||
39 | Philby | Closed Lost | 6/15/2021 | ||||||
40 | Philby | Closed Lost | 5/20/2021 | ||||||
41 | Philby | Closed Lost | 5/11/2021 | ||||||
42 | Philby | Closed Lost | 10/4/2021 | ||||||
43 | Philby | Closed Lost | 7/20/2021 | ||||||
44 | Philby | Closed Won | 10/15/2021 | ||||||
45 | Philby | Meeting Delivered | 12/22/2021 | ||||||
46 | Philby | Closed Lost | 8/3/2021 | ||||||
47 | Philby | Proposal Sent | 7/8/2021 | ||||||
48 | Philby | Demo Delivered | 12/7/2021 | ||||||
49 | Philby | Closed Lost | 7/6/2021 | ||||||
50 | Philby | Closed Lost | 5/13/2021 | ||||||
51 | Philby | Closed Lost | 11/19/2021 | ||||||
52 | Philby | Meeting Delivered | 11/17/2021 | ||||||
53 | Philby | Closed Won | 10/28/2021 | ||||||
54 | Philby | Closed Lost | 11/2/2021 | ||||||
55 | Philby | Closed Lost | 5/10/2021 | ||||||
56 | Philby | Closed Lost | 10/20/2021 | ||||||
57 | Philby | Discovery | 12/15/2021 | ||||||
58 | Philby | Closed Lost | 5/11/2021 | ||||||
59 | Philby | Closed Lost | 9/23/2021 | ||||||
60 | Philby | Closed Lost | 11/18/2021 | ||||||
61 | Philby | Closed Lost | 5/14/2021 | ||||||
62 | Philby | Closed Lost | 8/17/2021 | ||||||
63 | Philby | Closed Lost | 8/26/2021 | ||||||
64 | Philby | Closed Lost | 5/27/2021 | ||||||
65 | Philby | Closed Lost | 5/12/2021 | ||||||
66 | Philby | Closed Lost | 5/24/2021 | ||||||
67 | Philby | Closed Lost | 8/24/2021 | ||||||
68 | Philby | Closed Lost | 8/26/2021 | ||||||
69 | Philby | Closed Won | 6/7/2021 | ||||||
70 | Philby | Meeting Delivered | 11/8/2021 | ||||||
71 | Philby | Closed Lost | 5/7/2021 | ||||||
72 | Philby | Meeting Delivered | 10/26/2021 | ||||||
73 | Philby | Closed Won | 11/16/2021 | ||||||
74 | Philby | Closed Lost | 10/4/2021 | ||||||
75 | Philby | Closed Won | 5/13/2021 | ||||||
76 | Philby | Closed Lost | 8/17/2021 | ||||||
77 | Philby | Meeting Delivered | 10/22/2021 | ||||||
78 | Philby | Closed Won | 5/5/2021 | ||||||
79 | Philby | Closed Lost | 6/9/2021 | ||||||
80 | Philby | Closed Lost | 5/12/2021 | ||||||
81 | Philby | Discovery | 11/17/2021 | ||||||
82 | Philby | Solution Validation | 11/23/2021 | ||||||
83 | Philby | Discovery | 10/6/2021 | ||||||
84 | Philby | Demo Delivered | 5/6/2021 | ||||||
85 | Philby | Proposal Sent | 11/30/2021 | ||||||
86 | Philby | Meeting Delivered | 12/17/2021 | ||||||
87 | Philby | Closed Lost | 5/18/2021 | ||||||
88 | Philby | Closed Won | 5/11/2021 | ||||||
89 | Philby | Closed Lost | 10/6/2021 | ||||||
90 | Philby | Meeting Delivered | 12/14/2021 | ||||||
91 | Philby | Closed Lost | 12/7/2021 | ||||||
92 | Philby | Closed Lost | 5/19/2021 | ||||||
93 | Philby | Discovery | 11/29/2021 | ||||||
94 | Philby | Closed Lost | 10/6/2021 | ||||||
95 | Philby | Discovery | 12/10/2021 | ||||||
96 | Philby | Closed Lost | 12/7/2021 | ||||||
97 | Philby | Closed Lost | 10/8/2021 | ||||||
98 | Philby | Closed Lost | 11/3/2021 | ||||||
99 | Philby | Proposal Sent | 10/21/2021 | ||||||
100 | Philby | Closed Lost | 10/12/2021 | ||||||
101 | Philby | Closed Lost | 7/27/2021 | ||||||
102 | Philby | Closed Won | 9/15/2021 | ||||||
103 | Philby | Closed Lost | 5/6/2021 | ||||||
104 | Philby | Closed Lost | 8/27/2021 | ||||||
105 | Philby | Closed Lost | 9/15/2021 | ||||||
106 | Philby | Closed Lost | 7/21/2021 | ||||||
107 | Philby | Closed Lost | 9/29/2021 | ||||||
108 | Philby | Solution Validation | 5/26/2021 | ||||||
109 | Philby | Closed Lost | 5/7/2021 | ||||||
Philby |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B5 | B5 | =COUNTIFS(owner,$A$1,meetdate,">4/30/2021",meetdate,"<6/1/2021") |
B6 | B6 | =COUNTIFS(owner,$A$1,meetdate,">"&B3&B4,meetdate,"<6/1/2021") |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
meetdate | =Philby!$G$10:$G$109 | B5:B6 |
owner | =Philby!$E$10:$E$109 | B5:B6 |