Trouble referencing a "named range" (using variables within a Countifs formula

S

sccole

New Member
Joined
Aug 16, 2011
Messages
11
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
ABCDEFG
1Philby
2
3CriteriaFirst
4May21
5# Meetings (Original)27
6# Meetings (Problem)0
7
8Criteria TableData
9MonthFirstLastOwnerDeal StageMeetDate
10May4/30/20216/1/2021PhilbyMeeting Delivered11/29/2021
11June5/31/20217/1/2021PhilbyDemo Delivered11/24/2021
12July6/30/20218/1/2021PhilbyClosed Lost9/2/2021
13August7/31/20219/1/2021PhilbyClosed Lost7/22/2021
14September8/31/202110/1/2021PhilbyClosed Lost7/21/2021
15October9/30/202111/1/2021PhilbyClosed Lost7/6/2021
16November10/31/202112/1/2021PhilbyClosed Won5/14/2021
17December11/30/20211/1/2022PhilbyClosed Lost5/3/2021
18PhilbyClosed Lost5/18/2021
19PhilbyClosed Won10/21/2021
20PhilbyClosed Lost5/3/2021
21PhilbyDiscovery10/22/2021
22PhilbyClosed Lost6/15/2021
23PhilbyClosed Won8/3/2021
24PhilbyClosed Lost9/21/2021
25PhilbyClosed Lost10/29/2021
26PhilbyClosed Lost7/26/2021
27PhilbyClosed Lost7/9/2021
28PhilbyClosed Lost5/14/2021
29PhilbyClosed Lost7/27/2021
30PhilbyClosed Lost6/28/2021
31PhilbyClosed Lost9/21/2021
32PhilbyClosed Lost8/11/2021
33PhilbyClosed Lost11/2/2021
34PhilbyClosed Lost9/29/2021
35PhilbyDiscovery9/15/2021
36PhilbyClosed Lost5/24/2021
37PhilbyClosed Lost5/20/2021
38PhilbyClosed Won11/10/2021
39PhilbyClosed Lost6/15/2021
40PhilbyClosed Lost5/20/2021
41PhilbyClosed Lost5/11/2021
42PhilbyClosed Lost10/4/2021
43PhilbyClosed Lost7/20/2021
44PhilbyClosed Won10/15/2021
45PhilbyMeeting Delivered12/22/2021
46PhilbyClosed Lost8/3/2021
47PhilbyProposal Sent7/8/2021
48PhilbyDemo Delivered12/7/2021
49PhilbyClosed Lost7/6/2021
50PhilbyClosed Lost5/13/2021
51PhilbyClosed Lost11/19/2021
52PhilbyMeeting Delivered11/17/2021
53PhilbyClosed Won10/28/2021
54PhilbyClosed Lost11/2/2021
55PhilbyClosed Lost5/10/2021
56PhilbyClosed Lost10/20/2021
57PhilbyDiscovery12/15/2021
58PhilbyClosed Lost5/11/2021
59PhilbyClosed Lost9/23/2021
60PhilbyClosed Lost11/18/2021
61PhilbyClosed Lost5/14/2021
62PhilbyClosed Lost8/17/2021
63PhilbyClosed Lost8/26/2021
64PhilbyClosed Lost5/27/2021
65PhilbyClosed Lost5/12/2021
66PhilbyClosed Lost5/24/2021
67PhilbyClosed Lost8/24/2021
68PhilbyClosed Lost8/26/2021
69PhilbyClosed Won6/7/2021
70PhilbyMeeting Delivered11/8/2021
71PhilbyClosed Lost5/7/2021
72PhilbyMeeting Delivered10/26/2021
73PhilbyClosed Won11/16/2021
74PhilbyClosed Lost10/4/2021
75PhilbyClosed Won5/13/2021
76PhilbyClosed Lost8/17/2021
77PhilbyMeeting Delivered10/22/2021
78PhilbyClosed Won5/5/2021
79PhilbyClosed Lost6/9/2021
80PhilbyClosed Lost5/12/2021
81PhilbyDiscovery11/17/2021
82PhilbySolution Validation11/23/2021
83PhilbyDiscovery10/6/2021
84PhilbyDemo Delivered5/6/2021
85PhilbyProposal Sent11/30/2021
86PhilbyMeeting Delivered12/17/2021
87PhilbyClosed Lost5/18/2021
88PhilbyClosed Won5/11/2021
89PhilbyClosed Lost10/6/2021
90PhilbyMeeting Delivered12/14/2021
91PhilbyClosed Lost12/7/2021
92PhilbyClosed Lost5/19/2021
93PhilbyDiscovery11/29/2021
94PhilbyClosed Lost10/6/2021
95PhilbyDiscovery12/10/2021
96PhilbyClosed Lost12/7/2021
97PhilbyClosed Lost10/8/2021
98PhilbyClosed Lost11/3/2021
99PhilbyProposal Sent10/21/2021
100PhilbyClosed Lost10/12/2021
101PhilbyClosed Lost7/27/2021
102PhilbyClosed Won9/15/2021
103PhilbyClosed Lost5/6/2021
104PhilbyClosed Lost8/27/2021
105PhilbyClosed Lost9/15/2021
106PhilbyClosed Lost7/21/2021
107PhilbyClosed Lost9/29/2021
108PhilbySolution Validation5/26/2021
109PhilbyClosed Lost5/7/2021
Philby
Cell Formulas
RangeFormula
B5B5=COUNTIFS(owner,$A$1,meetdate,">4/30/2021",meetdate,"<6/1/2021")
B6B6=COUNTIFS(owner,$A$1,meetdate,">"&B3&B4,meetdate,"<6/1/2021")
Named Ranges
NameRefers ToCells
meetdate=Philby!$G$10:$G$109B5:B6
owner=Philby!$E$10:$E$109B5:B6
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Try changing criteria to May and 2021 an using the following formula:
Excel Formula: 
=COUNTIFS(owner,$A$1,meetdate,">="&EOMONTH(B3&B4,-1)+1,meetdate,"<"&EOMONTH(B3&B4,0)+1)
You won't need the Criteria Table
 
Upvote 0
Solution
Tetra201 said:
Try changing criteria to May and 2021 an using the following formula:
Excel Formula: 
=COUNTIFS(owner,$A$1,meetdate,">="&EOMONTH(B3&B4,-1)+1,meetdate,"<"&EOMONTH(B3&B4,0)+1)
You won't need the Criteria Table
Click to expand...
Thanks you very much! You simplified my problem - greatly appreciated.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
conditional formatting question
Replies
2
Views
153
kerm007
K
C
  • Solved
Return values based on date range
Replies
3
Views
152
Peter_SSs
Peter_SSs
omsumo
  • Solved
if a formula in excel with multible conditions
2
Replies
19
Views
372
arthurbr
arthurbr
imback2nite
  • Solved
VBA help.
2
Replies
10
Views
355
Peter_SSs
Peter_SSs
A
  • Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
243
amolvijay
A

Forum statistics

Threads
1,214,659
Messages
6,120,786
Members
448,992
Latest member
prabhuk279

Share this page

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