In a multi column database, assume a filter has been applied on some columns. Post filtering, some error values (such as #DIV/0!, #NA etc.) appear in the one of the numeric columns. The objective is to sum numbers in a numeric column. The usual SUM() function would not work because the range of sum function should be error free. Furthmore, […]
Read More →
{ 38 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 →
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 →
{ 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 →
{ 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 →
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 →
{ 0 Comments }
Assume multiple worksheets in a workbook (all having the same structure). In a summary sheet, one may want to tabulate data from these multiple worksheets. Essentially, a way to link to the same specific cells in multiple worksheets to the Summary sheet. The formula in the Summary sheet should just be copied and pasted to multiple rows to bring […]
Read More →
Sum visible cells of a filtered range ignoring errors
{ 4 Comments }