• Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us
Excel

Price SUM by weekdays in Excel

0 Comments

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

    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

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

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.
Rate this post
29
260 Views
How To Change The "Welcome" Text On The Computer Startup ScreenPrevHow To Change The "Welcome" Text On The Computer Startup ScreenJune 21, 2023
Combining First Name and Last Name in Microsoft Excel ( P1 )June 21, 2023Combining First Name and Last Name in Microsoft Excel ( P1 )Next

Leave a Reply Cancel reply

You must be logged in to post a comment.

Buy Windows 11 Professional MS Products CD Key
Buy Office 2021 Professional Plus Key Global For 5 PC
Top rated products
  • Kaspersky Internet Security 2021 1 year 5 devices key Global Kaspersky Internet Security 2021 1 year 5 devices key Global
    Rated 5.00 out of 5
    $45.00
  • Windows Server 2022 Datacenter Key Global Windows Server 2022 Datacenter Key Global
    Rated 5.00 out of 5
    $7.00
  • Kaspersky Small Office Security 10 PCs + 10 Mobiles + 1 Server 1 Year Kaspersky Small Office Security 10 PCs + 10 Mobiles + 1 Server 1 Year
    Rated 5.00 out of 5
    $164.50
  • Avast Ultimate Suite 2021 1 Year 10 Devices Global Avast Ultimate Suite 2021 1 Year 10 Devices Global
    Rated 5.00 out of 5
    $54.00
  • Windows 11 Pro Key Global Windows 11 Pro Key Global
    Rated 5.00 out of 5
    $6.00
Products
  • Windows Server 2012 R2 Datacenter Key Global Windows Server 2012 R2 Datacenter Key Global
    Rated 4.71 out of 5
    $15.00
  • Windows Server 2019 Remote Desktop Services Device Connections (50) Cal Key Global Windows Server 2019 Remote Desktop Services Device Connections (50) Cal Key Global
    Rated 4.72 out of 5
    $20.00
  • Windows 10 Education Key Global Windows 10 Education Key Global
    Rated 4.84 out of 5
    $9.00
  • Microsoft Project 2019 professional Key Global Bind to your Microsoft Account Microsoft Project 2019 professional Key Global Bind to your Microsoft Account
    Rated 4.93 out of 5
    $12.00
  • microsoft office home and business 2019 pcmac key Microsoft Office Home And Business 2019 (MAC) key bind to your Microsoft account
    Rated 4.95 out of 5
    $30.00
  • Buy Windows Server 2016 Essentials Key Global Buy Windows Server 2016 Essentials Key Global
    Rated 4.75 out of 5
    $10.00
  • Windows Server2008 R2 Remote Desktop Services device connections (20)cal Windows Server2008 R2 Remote Desktop Services device connections (20)cal $29.00
  • AVG Internet Security 2021 1 Device 1 Year Global AVG Internet Security 2021 1 Device 1 Year Global
    Rated 5.00 out of 5
    $11.00
  • Microsoft Project 2019 Professional Key Global Microsoft Project 2019 Professional - 5 PC
    Rated 4.97 out of 5
    $12.00
  • Windows Server 2025 Remote Desktop Services 50 Device Connections Windows Server 2025 Remote Desktop Services 50 Device Connections $25.00
Product categories
  • Anti Virus
  • Microsoft Office
  • Microsoft Project
  • Microsoft Visio
  • Microsoft Visual
  • Microsoft Windows
  • Other Software
  • Uncategorized

Buffcom.net always brings the best digital products and services to you. Specializing in Office Software and online marketing services

BIG SALE 50% IN MAY

Microsoft Office
Microsoft Windows
Anti-Virus
Contact Us

Visit Us:

125 Division St, New York, NY 10002, USA

Mail Us:

buffcom.net@gmail.com

TERMS & CONDITIONS | PAYMENT GUIDE  | SHIPPING POLICY  | REFUND POLICY

Copyright © 2019 buffcom.net  All Rights Reserved.