Best formula to use

RossR2071 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

RossR2071 

Here's a solution combining multiple formulas to achieve your goal of calculating workdays in a month and then summing different work types for each day:

Part 1: Counting Workdays in a Month

1. NETWORKDAYS Function: Use the NETWORKDAYS function to count the number of workdays in a specific month. This function excludes weekends (Saturday and Sunday) by default.

=NETWORKDAYS(DATE(YEAR(A2), MONTH(A2), 1), DATE(YEAR(A2), MONTH(A2), EOMONTH(A2)))

Explanation:

• A2: This cell contains the date for which you want to calculate workdays.
• YEAR(A2): Extracts the year from cell A2.
• MONTH(A2): Extracts the month from cell A2.
• 1: Represents the first day of the month.
• EOMONTH(A2): This function returns the last day of the month based on the date in A2.

Part 2: Summing Work Types per Day

1. SUMIFS Function: Use the SUMIFS function to sum specific work types for each day within the month, considering the following assumptions:

• You have a table with the following columns:
  • Date: This column contains dates for work activities.
  • Work Type 1: This column contains the type of work done (e.g., Meeting, Call, Task).
  • Work Type 2: This column contains another type of work done (e.g., Development, Review, Design).

1. Formula:

=SUMIFS({Work Type 1 Range}, {Date Range}, A2, {Work Type 2 Range}, B2)

Explanation:

• {Work Type 1 Range}: Replace this with the actual cell range containing the "Work Type 1" values.
• {Date Range}: Replace this with the actual cell range containing the dates.
• A2: This references the date cell for which you want to calculate the sum (same date used in the NETWORKDAYS function).
• {Work Type 2 Range}: Replace this with the actual cell range containing the "Work Type 2" values.
• B2: This references a cell containing the specific work type you want to sum for the date in A2. You can change this cell reference to another work type column to calculate its sum.

Putting it Together:

1. In a separate cell, use the NETWORKDAYS function to calculate the total workdays in the month.
2. Create a table with columns for "Date," "Work Type 1 Sum," and "Work Type 2 Sum" (or more depending on your needs).
3. In the "Date" column, list the dates for your work activities.
4. In the "Work Type 1 Sum" column, use the SUMIFS formula as described above, referencing the appropriate ranges and the date from the current row.
5. In the "Work Type 2 Sum" column, use the same SUMIFS formula structure but reference the "Work Type 2" range and adjust the cell reference for the specified work type you want to sum.
6. Drag the formulas down to subsequent rows to apply them to all dates in your table.