Imagine a dataset as shown below Status Text Number Active bat 1 Passive erq 2 Passive cat 3 Active enm 4 Active dog 5 Suppose the data is in range A3:C7 (headings are in A2:C2). The objective is to add numbers where the following conditions are met: 1. Status should be Active; and 2. Text […]
Read More →
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 →
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 →
{ 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 →
{ 0 Comments }
Data on the sheet named “Sales Data Master” shows the Territory wise, Show Title wise and Sales man wise forecast and actual data. One may want to summarise data based on an unknown combination of conditions. Conditions may be entered as: 1. All three conditions specified 2. No condition specified 3. Only first condition specified […]
Read More →
{ 0 Comments }
A number is entered in cell A1 of every sheet in a multi sheet workbook. The task is to sum numbers in cell C2 of the Summary sheet from cell A1 of last x sheets – x is the number of sheets (mentioned in cell B2 of the Summary sheet) from which numbers have to be summed […]
Read More →
{ 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 →
{ 12 Comments }
Assume a three column table arranged as follows: Circle, Date of Fault and ID. Dates in the date range span one week – November 26, 2012 to December 2, 2012. A particular equipment can be only one specific Region and the same equipment an go faulty multiple times within one week. Data for one week […]
Read More →
Assume a worksheet which has scores (1-10) on four future dates on nine questions. Each row represents responses for one question. Likewise there are 20 respondents i.e. 20 worksheets which have the same structure. One may want to summarise data from all 20 worksheets into one worksheet. The question and solution are more clearly explained in the attached […]
Read More →
{ 4 Comments }
In range G13:G17, assume the following entries – VG:25, NS:50, NS:20, #DIV/0! and VG:150. To sum up the values where VG appears, you may refer to my solution in this workbook.
Read More →
Summarise data with multiple wildcard OR conditions
{ 0 Comments }