Tags: SUMPRODUCT

Remove duplicates after satisfying additional conditions

{ 3 Comments }

Assume a two column database of patient ID’s and service availed.  One patient may avail the same service multiple times in a year due to which that record may appear as many times as the service is availed.  For e.g., if patient A001 avails the Radiology service twice, then A001 and Radiology will appear in two rows. […]

Read More →

Compute revenue with progressive discounting

{ 37 Comments }

Assume a two column database with airline names in column A and number of passenger seats in column B, one may want to know the total revenue.  Given a full rate of US$ 12.0 per seat, discounts are offered on this full rate depending upon the passenger seats booked.  So for example, for the first 500,000 […]

Read More →

Average a range of numbers with blanks appearing at random intervals

{ 4 Comments }

With blanks appearing in a range of numbers, one may want to: 1. Average the first n numbers; and/or 2. Sum the last n numbers (in a horizontal and vertical range) Because of blanks, the range for averaging will need to automatically keep expanding till the nth number is reached. You may refer to my solution in this […]

Read More →

SUMPRODUCT function to work on a range with interspersed text values

{ 0 Comments }

In a scenario where text values are interspersed with numeric data columns, the usual SUMPRODUCT function will not work.  One will have to use a combination of array formulas and the SUMPRODUCT function. You may refer to my solution in this workbook.

Read More →

Summarise data from multiple sheets with multiple conditions

{ 2 Comments }

Assume there are three worksheets, one each for 2008, 2009 and 2010.  On each sheet there are two columns for Account code and Account description.  Thereafter there are 12 columns (one for each month).  Values inside the matrix represent amount spent on a specific item in a specific month. On the P&L worksheet, a user will provide the […]

Read More →

Summarise data from multiple sheets with one condition

{ 6 Comments }

Assume there are ten worksheet tabs – one for each retail store.  Each worksheet tab has sale data of different brands across 12 months – brands are arranged in rows and months are arranged in columns.  The structure of all worksheets tabs is the same.  Furthermore, each store is managed in any of the two retail […]

Read More →

Extract data based on customer specific dimensions

{ 0 Comments }

Depending upon customer requirements specified for width, thickness and length os material, extract a report showing all records from the master stock-list which meets the requirements. The question and solution have been elaborately explained in this workbook.

Read More →

Apply the SUMPRODUCT() function on the visible cells of a filtered range

{ 0 Comments }

The SUMPRODUCT() function works on all cells in the specified range – whether filtered or not.  However, one may want to apply the same function on the visible cells of a filtered range. You may refer to my solution in this workbook.

Read More →