errollflynn
New Member
- Joined
- Jan 25, 2021
- Messages
- 24
- Office Version
- 365
- Platform
- Windows
Hello,
I've been banging my head against an issue I've been having with countifs. I have three ranges that I'm comparing.
The first column contains values 1-3 and I'm looking for 3. The second column contains the first day of a month i.e. 1/1/2021, 2/1/2021 etc. The third column also contains the first day of the month. I'm looking to count instances of where the first column has the value 3, the second has 6/1/2021 and the third has a date after the date in the second column. The formula I have is:
=COUNTIFS(Active!W3:W35,"3",Active!Z3:Z35,"6/1/2021",Active!AB3:AB35,">"&6/1/2021)
I'm getting 0 for a result. Based on how I understand the formula should work, row 9 should be counted.
Thank you for your help.
I've been banging my head against an issue I've been having with countifs. I have three ranges that I'm comparing.
The first column contains values 1-3 and I'm looking for 3. The second column contains the first day of a month i.e. 1/1/2021, 2/1/2021 etc. The third column also contains the first day of the month. I'm looking to count instances of where the first column has the value 3, the second has 6/1/2021 and the third has a date after the date in the second column. The formula I have is:
=COUNTIFS(Active!W3:W35,"3",Active!Z3:Z35,"6/1/2021",Active!AB3:AB35,">"&6/1/2021)
I'm getting 0 for a result. Based on how I understand the formula should work, row 9 should be counted.
COO Workforce Restoration Template MASTER 12.23.2020 JM.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
W | X | Y | Z | AA | AB | |||
3 | 1 | |||||||
4 | 2 | Cat 1 | 5/1/2021 | |||||
5 | 1 | |||||||
6 | 1 | |||||||
7 | 1 | |||||||
8 | 2 | Cat 1 | 7/1/2021 | |||||
9 | 3 | Cat 2 | 6/1/2021 | Cat 1 | 7/1/2021 | |||
10 | 1 | |||||||
11 | 1 | |||||||
12 | 1 | |||||||
13 | 1 | |||||||
14 | 1 | |||||||
15 | 1 | |||||||
16 | 1 | |||||||
17 | 1 | |||||||
18 | 3 | Cat 2 | 7/1/2021 | Cat 1 | 7/1/2021 | |||
19 | 1 | |||||||
20 | 1 | |||||||
21 | 1 | |||||||
22 | 1 | |||||||
23 | 2 | Cat 1 | 5/1/2021 | |||||
24 | 2 | Cat 1 | 8/1/2021 | |||||
25 | 3 | Cat 2 | 8/1/2021 | Cat 1 | 8/1/2021 | |||
26 | 1 | |||||||
27 | 2 | Cat 1 | 5/1/2021 | |||||
28 | 1 | |||||||
29 | 2 | Cat 1 | 7/1/2021 | |||||
30 | 3 | Cat 2 | 4/1/2021 | Cat 1 | 6/1/2021 | |||
31 | 1 | |||||||
32 | 1 | |||||||
33 | 2 | Cat 1 | 4/1/2021 | |||||
34 | 1 | |||||||
35 | 0 | |||||||
Active |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
Z21 | List | ='https://aegworldwide-my.sharepoint.com/personal/jmelendez_aegpresents_com/Documents/Mstr Cmpy Ovw 2020/WF Restoration Plan 2021/[Mkt Workforce Restoration Template MASTER 12.23.2020 BMK.xlsx]Date Criteria'!#REF! |
Z4 | List | ='Date Criteria'!$A$2:$A$14 |
Z6 | List | ='Date Criteria'!$A$2:$A$14 |
Z13 | List | ='Date Criteria'!$A$2:$A$14 |
Z25:Z35 | List | ='Date Criteria'!$A$2:$A$14 |
AB11 | List | ='Date Criteria'!$A$2:$A$14 |
AB6 | List | ='Date Criteria'!$A$2:$A$14 |
Z15 | List | ='Date Criteria'!$A$2:$A$14 |
AB32:AB35 | List | ='Date Criteria'!$A$2:$A$14 |
AB9 | List | ='Date Criteria'!$A$2:$A$14 |
AB18 | List | ='Date Criteria'!$A$2:$A$14 |
AB25 | List | ='Date Criteria'!$A$2:$A$14 |
AB30 | List | ='Date Criteria'!$A$2:$A$14 |
Z8:Z9 | List | ='Date Criteria'!$A$2:$A$14 |
Z18 | List | ='Date Criteria'!$A$2:$A$14 |
Thank you for your help.