Hits: 12

# (Excel examples for Beginners)

In this end-to-end excel example, you will learn – Excel formula for Beginners – How to Sum by week number in Excel.

#### Free Machine Learning & Data Science Coding Tutorials in Python & R for Beginners. Subscribe @ Western Australian Center for Applied Machine Learning & Data Science.

Western Australian Center for Applied Machine Learning & Data Science – Membership

# Excel formula for Beginners – How to Sum by week number in Excel

### Generic formula

=SUMIFS(sumrange,weekrange,week)

### Explanation

To sum by week number, you can use a formula based on the SUMIFS function. In the example shown, the formula in H5 is:

=SUMIFS(total,color,$G5,week,H$4)

where **total** (D5:D16), **color** (B5:B16), and **week** (E5:E16) are named ranges.

### How this formula works

The SUMIFS function can sum ranges based on multiple criteria.

In this problem, we configure SUMIFS to sum amounts in the named range **total** by week number using two criteria:

- color = value in column G
- week = value in row 4

=SUMIFS(total,color,$G5,week,H$4)

Note $G5 and H$4 are both mixed references, so the formula can be copied across the table.

Column E is a helper column, and contains the WEEKNUM function:

=WEEKNUM(C5)

Week numbers in row 4 are numeric values, formatted with a special custom number format :

"Wk "0 // display as Wk 1, Wk 2, etc.

This makes it possible to match week numbers in row 4 with week numbers in column E.

#### Free Machine Learning & Data Science Coding Tutorials in Python & R for Beginners. Subscribe @ Western Australian Center for Applied Machine Learning & Data Science.

Western Australian Center for Applied Machine Learning & Data Science – Membership