How to sum if with week numbers in Excel

In this article, we will learn How to sum with week numbers in Excel.

Scenario:

In simple words, while working with data tables, sometimes we need to get the sum of the values where the values belong to the same week. For example, finding the sum of groceries bought each week. Basically to find the sum of values segregated by weeks. Below is the formula explWEEKNUMained with an example.

How to solve the problem?

For this we will be using the basic SUMIFS function. SUMIFS function returns the sum of values corresponding to the matched criteria. Criteria can be given inside the formula. Here we need to divide the problem in two parts. If data is given with dates, We need to extract the week number of the date first, then proceed on to the SUMIFS formula.

The WEEKNUM function checks the date_value and returns its week number in the cell.

WEEKNUM formula:

=WEEKNUM (date_value)

Date_value : date value, for the criteria range

 

SUMIFS formula:

=SUMIFS(sum_range, weeknum array, week_number, [criteria_range2, criteria2],..... )

sum_range : range, where sum is required

weeknum array : weeknum array, as criteria range

week_number : week number, criteria to match

criteria_range2 : 2nd criteria range 

criteria2 : 2nd criteria

Example :

All of these might be confusing to understand. Let's understand it with an example here. Here we have data of orders recieved with dates, quantity and Price values. We need to sum the Price values with given criteria. For this first we will create a new column called weeknum that will be our criteria range.

To get the week numbers of the date values, we will index a new column Weeknum and use the formula in the first cell.

Use the formula :

=WEEKNUM(B2)

Copy the formula to rest of cells using the shortcut Ctrl + D or dragging down the right bottom corner of the B2 cell.

As you can see, we got all the week numbers corresponding to the date values. Now we use this week number array as criteria range in the below formula

Use the formula:

=SUMIFS ( D2:D17, E2:E17, H6)

Explanation:

  1. Here criteria is 1 and criteria range is weeknum array.
  2. The SUMIFS function returns the sum of Price values corresponding to the matched week numbers in the weeknum array.

As you can see the formula returns the sum of Price values corresponding to the 1 in weeknum array $58.41 + $88.25 + 302.63 = $450.29.

Now to get the sum of rest of the weeks via changing the weeknum criteria value. 


As you can see the formula works fine. We can use the SUMIF function, if there is no other criteria to be used. Below is the formula for the if you want to use the SUMIF function for the same data.

Use the formula:

=SUMIF( E2:E17, H6, D2:D17)

As you can see the SUMIF function also returns the same result. But for this formula you will be needing the weeknum array. Be careful using the SUMIF or SUMIFS function, generally people get confused with the SUMIF and SUMIFS syntax. SUMIF function has sum range as the last argument, whereas SUMIFS has sum range as the first argument.

Here are all the observational notes regarding using the formula.

Notes:

  1. The formula only works with numbers.
  2. The two ranges provided in the formula must have the same number of rows or the formula returns error.
  3. The SUMIF function considers non - numeric values as 0s.
  4. The SUMIF function considers logic value TRUE as 1 and False as 0.
  5. Operators like equals to ( = ), less than equal to ( <= ), greater than ( > ) or not equals to ( <> ) can be performed within a formula applied, with numbers only.

Hope this article about How to sum if with week numbers in Excel is explanatory. Find more articles on SUMMING formulas here. If you liked our blogs, share it with your fristarts on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 

Related Articles

How to use the SUMPRODUCT function in Excel: Returns the SUM after multiplication of values in multiple arrays in excel.

SUM if date is between : Returns the SUM of values between given dates or period in excel.

Sum if date is greater than given date: Returns the SUM of values after the given date or period in excel.

2 Ways to Sum by Month in Excel: Returns the SUM of values within a given specific month in excel.

How to Sum Multiple Columns with Condition: Returns the SUM of values across multiple columns having condition in excel

How to use wildcards in excel : Count cells matching phrases using the wildcards in excel

Popular Articles:

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets. 

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube