week number

4 WAYS TO SUM DATA BY WEEK NUMBER

Crispo Mwangi | 8-Feb-18 | Leave a Comment

Every organization requires weekly summaries to monitor peak and off-peak periods. This is mostly so when you are in the production section. Excel has a beautiful but overlooked function (WEEKNUM) that returns an integer representing the week in the year (1 to 53). For anyone not familiar with WEEKNUM function, it contains 2 arguments; Serial_number ( […]

FIND N using multiple criteria

FIND THE LAST OR Nth OCCURENCE IN EXCEL USING MULTIPLE CRITERIA

Crispo Mwangi | 3-Feb-17 | 1 Comment

In the previous article, we looked at 7 ways to find the last or Nth occurrence in a Sorted list in excel. Unsorted list poses a challenge since you need to find the occurrence using multiple criteria. To show the different methods I have created a list of Customers and their Purchase Orders (P.O). You can download […]

2 WAY LOOKUP IN EXCEL

Crispo Mwangi | 20-Aug-16 | 2 Comments

The ability to lookup values is a MUST have skills for all Excel users. If you cannot lookup you cannot excel in data analysis. In this article, I will show you  5  ways you can do a 2-way lookup in Excel. 1. USING SUMPRODUCT Using below sales data lookup and sum the total quantity for ALL items ordered […]

lookup and Sum

LOOKUP and SUM IN EXCEL

Crispo Mwangi | 7-Aug-16 | 1 Comment

Array fomulas comes in handy when you want to lookup and sum large amount of data. Array formulas replaces multiple normal formulas,performs multiple calculations and returns a single value as a result. For example using  below Main sales data in Quantity and a lookup price table, Calculate total sales for customer Brosina Hoffman?   There […]

COUNT UNIQUE DUPLICATE VALUES IN EXCEL

Crispo Mwangi | 30-Jul-16 | 4 Comments

COUNTIF is an excellent function to count only those cells whose  value meets a certain criteria. But as excellent as it is, it becomes a challenge to count Unique or Repeats in a range that contain duplicates. For example, Using below data count; Total number of unique customers Total Non-Repeat Customers Total Repeat customers There are 4 ways of counting […]