jski21
Board Regular
- Joined
- Jan 2, 2019
- Messages
- 155
- Office Version
- 2016
- Platform
- Windows
Good day Mr. Excel Team,
Struggling with getting my COUNTIFS formula in Cell I20 to work. The result should be 4 but it's returning 0. Cells are formatted as dates so what am I missing?
Thanks in adavance for the look/see and advice.
jski
Struggling with getting my COUNTIFS formula in Cell I20 to work. The result should be 4 but it's returning 0. Cells are formatted as dates so what am I missing?
| Mr Excel.xlsx | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | ||||||||||||||
| 2 | Expenses Tab | |||||||||||||
| 3 | Max date: | 10-21-2021 | ||||||||||||
| 4 | Min date: | 08-27-2014 | Date (MM-DD-YYYY) | Store / Vendor | $ Amount | Expense Category | ||||||||
| 5 | 2/22/2021 | 6320 - Professional Services | $5,939.81 | AAA | ||||||||||
| 6 | Income Tab | 2/22/2021 | 6320 - Professional Services | $11,815.00 | BBB | |||||||||
| 7 | Max date: | 01-00-1900 | 2/22/2021 | 6320 - Professional Services | $36,601.29 | BBB | ||||||||
| 8 | Min date: | 01-00-1900 | 3/30/2021 | 6320 - Professional Services | $57,902.13 | CCC | ||||||||
| 9 | 10/21/2021 | 6320 - Professional Services | $3,105.16 | BBB | ||||||||||
| 10 | 10/21/2021 | 6320 - Professional Services | $7,322.51 | BBB | ||||||||||
| 11 | Overall Time Period | |||||||||||||
| 12 | ||||||||||||||
| 13 | Transaction Dates | Expenses | Income | Overall | Month | Year | ||||||||
| 14 | Oldest | 08-27-2014 | na | 08-27-2014 | 8 | 2014 | ||||||||
| 15 | Newest | 10-21-2021 | na | 10-21-2021 | 10 | 2021 | ||||||||
| 16 | ||||||||||||||
| 17 | ||||||||||||||
| 18 | Dashboard Time Periods | BBB | AAA | CCC | ||||||||||
| 19 | ||||||||||||||
| 20 | Period | Start Date | End Date | Text | Count | 0 | 0 | 0 | ||||||
| 21 | Latest month | 10-01-2021 | 10-31-2021 | Latest month (Oct-21) | Average | |||||||||
| 22 | Last 3 months | 08-01-2021 | 10-31-2021 | Last 3 months (Aug-21 to Oct-21) | Maximum | |||||||||
| 23 | Last 6 months | 05-01-2021 | 10-31-2021 | Last 6 months (May-21 to Oct-21) | Minimum | |||||||||
| 24 | Last 12 months | 11-01-2020 | 10-31-2021 | Last 12 months (Nov-20 to Oct-21) | ||||||||||
| 25 | Year to date | 01-01-2021 | 10-31-2021 | Year to date (Jan-21 to Oct-21) | ||||||||||
| 26 | All | 08-27-2014 | 10-21-2021 | All (Aug-14 to Oct-21) | ||||||||||
| 27 | ||||||||||||||
| 28 | Selected time period | # of months | ||||||||||||
| 29 | 4 | Last 12 months | 11-01-2020 | 10-31-2021 | 12.00 | |||||||||
| 30 | ||||||||||||||
| 31 | Selected Label: | (12 months) | ||||||||||||
Date Info | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C3 | C3 | =MAX(IF('\\CD-SRV2\Budget_Accounting\Jski\Excel\[Public Works Dashboard.xlsx]Expenses'!B:B>0,IF('\\CD-SRV2\Budget_Accounting\Jski\Excel\[Public Works Dashboard.xlsx]Expenses'!F:F<>"Y",'\\CD-SRV2\Budget_Accounting\Jski\Excel\[Public Works Dashboard.xlsx]Expenses'!B:B))) |
| C4 | C4 | =MIN(IF('\\CD-SRV2\Budget_Accounting\Jski\Excel\[Public Works Dashboard.xlsx]Expenses'!B:B>0,IF('\\CD-SRV2\Budget_Accounting\Jski\Excel\[Public Works Dashboard.xlsx]Expenses'!F:F<>"Y",'\\CD-SRV2\Budget_Accounting\Jski\Excel\[Public Works Dashboard.xlsx]Expenses'!B:B))) |
| C7 | C7 | =MAX(IF('\\CD-SRV2\Budget_Accounting\Jski\Excel\[Public Works Dashboard.xlsx]Income'!B:B>0,IF('\\CD-SRV2\Budget_Accounting\Jski\Excel\[Public Works Dashboard.xlsx]Income'!F:F<>"Y",'\\CD-SRV2\Budget_Accounting\Jski\Excel\[Public Works Dashboard.xlsx]Income'!B:B))) |
| C8 | C8 | =MIN(IF('\\CD-SRV2\Budget_Accounting\Jski\Excel\[Public Works Dashboard.xlsx]Income'!B:B>0,IF('\\CD-SRV2\Budget_Accounting\Jski\Excel\[Public Works Dashboard.xlsx]Income'!F:F<>"Y",'\\CD-SRV2\Budget_Accounting\Jski\Excel\[Public Works Dashboard.xlsx]Income'!B:B))) |
| C14 | C14 | =IF('Date Info'!C4=0,"na",'Date Info'!C4) |
| D14 | D14 | =IF('Date Info'!C8=0,"na",'Date Info'!C8) |
| E14 | E14 | =IF(MIN(C14:D14)=0,"na",MIN(C14:D14)) |
| F14:F15 | F14 | =IFERROR(MONTH(E14),"na") |
| G14:G15 | G14 | =IFERROR(YEAR(E14),"na") |
| C15 | C15 | =IF('Date Info'!C3=0,"na",'Date Info'!C3) |
| D15 | D15 | =IF('Date Info'!C7=0,"na",'Date Info'!C7) |
| E15 | E15 | =IF(MAX(C15:D15)=0,"na",MAX(C15:D15)) |
| I20 | I20 | =COUNTIFS(L5:L10,I18,I5:I10,”>=”&C29,I5:I10,”<=”&D29) |
| J20 | J20 | =COUNTIFS(L5:L10,J18,I5:I10,”>=”&C29,I5:I10,”<=”&D29) |
| L20 | L20 | =COUNTIFS(L5:L10,K18,I5:I10,”>=”&C29,I5:I10,”<=”&D29) |
| C21 | C21 | =IFERROR(DATE(YEAR(D21),MONTH(D21),1),"na") |
| D21 | D21 | =IFERROR(EOMONTH(E15,0),"na") |
| E21 | E21 | =B21&" ("&TEXT(C21,"mmm-yy")&")" |
| C22 | C22 | =IFERROR(DATE(YEAR(D22),MONTH(D22)-2,1),"na") |
| D22:D25 | D22 | =$D$21 |
| E22:E26 | E22 | =B22&" ("&TEXT(C22,"mmm-yy")&" to "&TEXT(D22,"mmm-yy")&")" |
| C23 | C23 | =IFERROR(DATE(YEAR(D23),MONTH(D23)-5,1),"na") |
| C24 | C24 | =IFERROR(DATE(YEAR(D24),MONTH(D24)-11,1),"na") |
| C25 | C25 | =IFERROR(DATE(YEAR(D25),1,1),"na") |
| C26 | C26 | =E14 |
| D26 | D26 | =E15 |
| A29 | A29 | ='\\CD-SRV2\Budget_Accounting\Jski\Excel\[Public Works Dashboard.xlsx]Dashboard'!B3 |
| B29 | B29 | =OFFSET(B$20,$A29,0) |
| C29 | C29 | =IFERROR(IF($A$29<7,DATE(YEAR(OFFSET(C$20,$A29,0)),MONTH(OFFSET(C$20,$A29,0)),1),OFFSET(C$20,$A29,0)),"na") |
| D29 | D29 | =IFERROR(IF($A$29<7,EOMONTH(OFFSET(D$20,$A29,0),0),OFFSET(D$20,$A29,0)),"na") |
| E29 | E29 | =IFERROR(MAX(((YEAR(D29)-YEAR(C29))*12+MONTH(D29)-MONTH(C29)+1),1),1) |
| C31 | C31 | ="("&IF(ROUND($E$29,0)=1,TEXT($E$29,"#,##0")&" month",TEXT($E$29,"#,##0")&" months")&")" |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| I5:I10 | Date | >1/1/1900 |
Thanks in adavance for the look/see and advice.
jski
