Price SUM by weekdays in Excel

Price SUM by weekdays in Excel. In this article, we will learn how to calculate the sum of prices by weekdays in Excel.

Scenario: When working with price data, there are times when we need to find the sum of price values based on specific weekdays. The condition is to consider the given weekday and calculate the sum of corresponding price values. The formula below demonstrates how to achieve this.

Price SUM by weekdays in Excel

How to solve the problem: To solve this problem, we will use the following functions:

• SUMPRODUCT function
• WEEKDAY function

We will combine these functions to create a formula. We will provide the necessary data and calculate the sum based on the given weekday_num.

Syntax of the formula: =SUMPRODUCT((WEEKDAY(dates) = weekday_num) * price)

Explanation of terms:

• Dates: Date values provided as cell references or using named ranges.
• Weekday_num: Number representing the weekday of the week.
• Price: Amounts or price values to be added.
  

  Price SUM by weekdays in Excel

Example: To better understand the concept, let’s test the formula with an example. Assume we have data with the prices of products received on different dates. We want to find the sum of prices for all seven weekdays. To achieve this, we assign weekday_nums in a column for the formula. The formula to calculate the sum of prices is as follows:

Formula: =SUMPRODUCT((WEEKDAY(dates) = G2) * price)

Explanation: The WEEKDAY function checks if the week_num in G2 matches the weekday of each date and returns TRUE or FALSE for the array of dates. The SUMPRODUCT function assigns a value of 1 for every TRUE value and 0 for every FALSE value. The formula then multiplies the resulting array of 1s and 0s with the price values and adds them up to calculate the sum.

By providing the named range “dates” for the date range C2:C10, the cell reference G2 for the value in G2, and the named range “price” for the price range D2:D10, we can obtain the sum of prices for the specified weekday.

Price SUM by weekdays in Excel

Additionally, if you want to find the sum of values excluding a specific weekday, you can use the NOT function in combination with the WEEKDAY function. The formula would be:

Price SUM by weekdays in Excel

Formula: =SUMPRODUCT(NOT((WEEKDAY(dates) = G2)) * price)

The NOT function negates the result of the WEEKDAY function, turning 0s to 1s and 1s to 0s. The rest of the formula remains the same, allowing you to calculate the sum without including the specified weekday.

Notes:

• The SUMPRODUCT function treats non-numeric values as 0s.
• The SUMPRODUCT function interprets the logical value TRUE as 1 and FALSE as 0.
• The arrays provided as arguments must have the same length, or else the function will return an error.