Tags: SUMPRODUCT

Compute configuration count using Set Theory and Venn Diagrams

{ 0 Comments }

Assume a table which lists attendees for a Company’s Annual day function.  In this Table, data for every attendee is shown on a separate row so if an employee attends the function with his/her spouse and three children, then there will be 5 rows for that employee. The question is to determine the count of the following family configuration: […]

Read More →

Perform an iterative sum of Top n values across multiple columns

{ 4 Comments }

A tournament has 18 participating teams with 25 players in each team.  Each team has to play five rounds of the Tournament and not all players play all rounds.  Scores earned by each player in each round are shown in individual cells.  If a player does not play a round, that cell is left empty. The task […]

Read More →

Determine cumulative expenses per employee when per diem rates vary by block of dates

{ 10 Comments }

Assume per diem travel rates vary by block of dates (from and to).  So, assume the per diem rate for travel dates between 26/2/2013 and 28/2/2013 is Rs. 78,000/day.  Likewise, if a person travels between 1/3/2013 and 25/3/2013, the per diem rate applicable is Rs. 70,000/day.  With different travel dates (from and to) specified per traveller, the […]

Read More →

Summarise data from multiple sheets with multiple conditions – Part II

{ 64 Comments }

Assume date wise revenue data has been shown on two sheets – Sports wear and Fitness equipment (Data worksheets).  On each of these two worksheets, there are two columns – Date and Revenue.  On the Summary worksheet, the user would like to summarise data (Count instances and sum revenue figures) based on multiple conditions.  The […]

Read More →

Show granular as well as total figures on the Summary sheet

{ 0 Comments }

Assume there is data for complaints received and complaints resolved for two years – 2009 and 2010 for some regions regions.  Each Region has its own worksheet.  There are three headings on each sheet – Particulars, 2009 and 2010.  While the Particulars column has the text “Complaints received” and “Complaints resolved”, the year columns have number of complaints received […]

Read More →

Count unique values with conditions on large databases

{ 8 Comments }

Given a database of 50,000 rows, counting unique values with conditions using formulas would either adversely effect workbook performance or would not work in the first place at all. In this workbook, I have shown the technique to count unique values with conditions on a large database 1. Using PowerPivot – Will only work in […]

Read More →

Create employee wise Effort Utilisation Report

{ 0 Comments }

Assume a “Labour utilisation” report which shows day wise tasks performed by different employees (row labels are task names, column labels are days and data in the matrix shows employee names).  Each employee can perform multiple tasks on the same day and same task on multiple days. Given a list of all employees, one may want to answer […]

Read More →

Summarise data from different cells of multiple worksheets

{ 12 Comments }

In each of the four worksheets (one for each week) of this workbook, there is a two column database showing Employee ID and Revenue earned by that employee in that week.  Not all employees appear on all sheets and there is no fixed cell reference for any employee on any sheet – therefore employee A001 can […]

Read More →

Extract specific number of characters from an alphanumeric string without breaking any word

{ 4 Comments }

When downloading data from an ERP into Excel, a Remarks column has entries which can be as lengthy as 300 characters per cell.  Before uploading this data into another database, entries in the Remarks column need to be split into multiple cells of upto 50 characters per cell.  Furthermore, when splitting data into multiple cells, it should […]

Read More →