## You are learning SUM in MS Excel

How to sum based on weekdays or weekends?

Here are two common ways to calculate the sum based on weekdays or weekends in Excel:

Method 1: Using SUMIFS and WEEKDAY functions (works for Excel 2007 and later)

1. Identify your data range: Determine the range of cells containing the values you want to sum. Let's say your data is in cells A1:A20.

2. Weekday criteria: Decide which numeric value represents weekdays (typically 1 to 5) and weekends (typically 6 or 7). Weekday functions can vary slightly depending on your Excel version, so double-check the specific output for weekdays and weekends.

3. Create separate formulas: You'll need two formulas, one for weekdays and one for weekends.

* Weekday Sum: In a separate cell (e.g., B1), enter the formula:

`=SUMIFS(A1:A20, WEEKDAY(A1:A20,2)<=5, A1:A20)`

- This formula uses SUMIFS to sum the values in A1:A20 (replace with your actual range) where the corresponding weekday number in the same range (WEEKDAY(A1:A20,2)) is less than or equal to 5 (weekdays).

* Weekend Sum: In another cell (e.g., B2), enter the formula:

`=SUMIFS(A1:A20, WEEKDAY(A1:A20,2)>5, A1:A20)`

- This formula uses SUMIFS again to sum the values in A1:A20 where the corresponding weekday number is greater than 5 (weekends).

Method 2: Using SUMPRODUCT and WEEKDAY functions (more versatile)

1. Identify your data range: Similar to method 1, determine the range of cells containing the values (e.g., A1:A20).

2. Weekday criteria: Decide on the weekday and weekend numeric representations.

3. Weekday Sum: In a separate cell (e.g., B1), enter the formula:

`=SUMPRODUCT((WEEKDAY(A1:A20,2)<=5)*A1:A20)`

- This formula uses SUMPRODUCT to multiply an array of TRUE/FALSE values (based on weekdays <= 5) with the corresponding data values in your range. TRUE values (weekdays) are then summed along with their corresponding data.

* Weekend Sum: In another cell (e.g., B2), enter the formula:

`=SUMPRODUCT((WEEKDAY(A1:A20,2)>5)*A1:A20)`

- This formula uses SUMPRODUCT similarly to the weekday sum, but checks for weekdays greater than 5 (weekends).

Both methods achieve the same result of separate sums for weekdays and weekends. Choose the method that best suits your comfort level or specific needs.