jjenkins104
New Member
- Joined
- Jan 3, 2025
- Messages
- 1
- Office Version
- 2024
- Platform
- Windows
Hi, can someone please help a guy out? I am trying to create a countifs formula that will tell me how many full tasks Bill completed in the month of January.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Countifs question
- Thread starter jjenkins104
- Start date
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 74,415
- Office Version
- 365
- Platform
- Windows
Welcome to the Board!
With COUNTIFS, you can have multiple conditions. So you can check by user and by dates.
This link shows you how to get the count between two dates: Count cells between dates
With COUNTIFS, you can have multiple conditions. So you can check by user and by dates.
This link shows you how to get the count between two dates: Count cells between dates
Upvote
0
Hello Jenkins,
Welcome to the forum. It's best to share your sample data using the forums Excel Add-In here:
XL2BB - Excel Range to BBCode
Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
However, I have something that might work for you. I just cobbled it together from an Attendence Tracker I created a few months ago... with help I've learned from here. @Joe4 being one of them.
Just Copy/Paste this into a new sheet and play with it's functionality. I'm using drop-down lists to make selecting your criteria a little bit easier. Right now, your request describes a solution that hardcodes the Name and Type into the formula. The year can just be entered normally.
Select Month, Name and Type from their respective drop-down lists, and your results will be counted.
| VBA Testing.xlsm | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| E | F | G | H | I | J | K | L | M | N | O | P | |||
| 1 | Date | Name | Type | Year: | 2025 | Months | Names | Type | ||||||
| 2 | 2025-01-25 | Bill | Full | Month: | January | January | Bill | Full | ||||||
| 3 | 2025-01-11 | Bill | Partial | Name: | Bill | February | Jim | Partial | ||||||
| 4 | 2025-01-30 | Bill | Full | Type: | Full | March | ||||||||
| 5 | 2025-01-19 | Bill | Full | Total: | 5 | April | ||||||||
| 6 | 2025-01-02 | Jim | Full | May | ||||||||||
| 7 | 2025-01-30 | Bill | Partial | June | ||||||||||
| 8 | 2025-01-19 | Bill | Full | July | ||||||||||
| 9 | 2025-01-02 | Jim | Partial | August | ||||||||||
| 10 | 2025-01-30 | Bill | Full | September | ||||||||||
| 11 | October | |||||||||||||
| 12 | November | |||||||||||||
| 13 | December | |||||||||||||
Count | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J5 | J5 | =LET( StartDate, DATE($J$1,$J$2,1), EndDate, DATE($J$1,$J$2,EOMONTH(DATE($J$1,$J$2,1),$J$2)), Name, $J$3, Type, $J$4, COUNTIFS(t_Tasks[Date],">="&StartDate,t_Tasks[Date],"<"&EndDate,t_Tasks[Name],"="&Name,t_Tasks[Type],"="&Type)) |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| J2 | List | =INDIRECT("t_Months[Months]") |
| J3 | List | =INDIRECT("t_Names[Names]") |
| J4 | List | =INDIRECT("t_Type[Type]") |
Upvote
0
I would recommend creating a Sheet called Lists and putting those lists (Months, Names and Type) on that Sheet.
They don't need to be seen. Just keep those tables/lists updated with new critieria so they are available in the drop-down lists.
They don't sort automatically, so be sure to sort those List/Tables as you add new data to them.
They don't need to be seen. Just keep those tables/lists updated with new critieria so they are available in the drop-down lists.
They don't sort automatically, so be sure to sort those List/Tables as you add new data to them.
Upvote
0
