Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Merge tables with VLOOKUP in Excel
  • Create hyperlink with VLOOKUP in Excel
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation

Data Analysis Examples

  • How to combine 2 or more chart types in a single chart in Excel
  • Remove Duplicates Example in Excel
  • How to Create Area Chart in Excel
  • How To Compare Two Lists in Excel
  • How to count table rows in Excel

Data Validation Examples

  • Excel Data validation only dates between
  • Excel Data validation must not contain
  • Excel Data validation exists in list
  • Excel Data validation number multiple 100
  • Excel Data validation allow uppercase only

Sum by weekday in Excel

by

This tutorial shows how to Sum by weekday in Excel using the example below;

Formula

=SUMPRODUCT((WEEKDAY(dates)=day_num)*values)

Explanation

To sum data by weekday (i.e. sum by Mondays, Tuesdays, Wednesdays, etc.), you can use the SUMPRODUCT function together with the WEEKDAY function.

In the example shown, the formula in H4 is:

=SUMPRODUCT((WEEKDAY(dates,2)=G4)*amts)

How this formula works

You might wonder why we aren’t using the SUMIF or SUMIFS function? These appear to be an obvious way to sum by the days of the week. However, without adding a helper column with a weekday value, there is no way to create a criteria for SUMIF that takes into account the weekday.

Worked Example:   How to count specific words in a range in Excel

Instead, we use the handy SUMPRODUCT function, which handles arrays gracefully without the need to use Control + Shift + Enter.

We are using SUMPRODUCT with just one argument, which consists of this expression:

(WEEKDAY(dates,2)=G4)*amts

Working from the inside out, the WEEKDAY function is configured with the optional argument 2, which causes it to return numbers 1-7 for the days Monday-Sunday, respectively. This is not necessary, but it makes it easier to list the days in order and pick up the numbers in column G in sequence.

Worked Example:   Count rows with multiple OR criteria in Excel

WEEKDAY evaluates each value in the named range “dates” and returns a number. The result is an array like this:

{3;5;3;1;2;2;4;2}

The numbers returned by WEEKDAY are then compared to the value in G4, which is 1.

Worked Example:   OR function Examples in Excel

{3;5;3;1;2;2;4;2}=1

The result is an array of TRUE/FALSE values.

{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}

Next, this array is multiplied by the values in the named range “amts”.  SUMPRODUCT only works with numbers (not text or booleans) but math operations automatically coerce the TRUE/FALSE values to one’s and zeros, so we have:

{0;0;0;1;0;0;0;0}*{100;250;75;275;250;100;300;125}

Which yields:

{0;0;0;275;0;0;0;0}

With just this single array to process, SUMPRODUCT sums the items and returns the result.

Post navigation

Previous Post:

Sum entire column in Excel

Next Post:

Sum columns based on adjacent criteria in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to check if cell contains one of many things in Excel
  • Extract word that begins with specific character in Excel
  • How to calculate Number of Instances in Excel
  • Two ways to Compare Text in Excel
  • FIND, FINDB functions: Description, Usage, Syntax, Examples and Explanation
  • Add decimal minutes to time in Excel
  • Dynamic date list in Excel
  • Get age from birthday in Excel
  • Assign points based on late time in Excel
  • Create date range from two dates in Excel
  • Calculate interest rate for loan in Excel
  • AMORDEGRC function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate Net Present Value (NPV) in Excel
  • Future value vs. Present value examples in Excel
  • PV function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles