Tags: COUNTIF

Determine number of learners who have completed different stages of multiple online courses

{ 4 Comments }

Here is a sample dataset of learners who have cleared different stages of multiple courses on offer within an Organisation: Learner Stage completed Course Bill Stage 1 Public Speaking Bill Stage 2 Public Speaking Bill Stage 3 Public Speaking Susan Stage 1 Effective Communication Bob Stage 1 Public Speaking Bob Stage 2 Public Speaking Sheila […]

Read More →

Create a Pivot Table from multiple individual ranges without using ancillary columns

{ 16 Comments }

Assume that there are three separate tables showing the following information: 1. Date of visit data for visitors to a certain recreation facility.  The same visitor may visit the facility multiple times 2. Fee per visit in different cites 3. Region in which each city lies The question is to analyse the three tables above via […]

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 →

Generate a list of missing invoice numbers

{ 2 Comments }

In a range of cells, assume there is a list of invoices raised during a certain period.  Since adequate care was not taken to number the invoices sequentially, there are missing invoice numbers in the Excel range.  So, it is possible that after raising invoice number 501, 502 and 503 the next invoice raised was 507. Given […]

Read More →

Sum data from a particular cell of last n sheets only

{ 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 →

Count text values in a range

{ 0 Comments }

In range C4:C10, assume the following values/formulas 1. Cell C4 – 1 2. Cell C5 – 2 3. Cell C6 – W 4. Cell C7 – We 5. Cell C8 – Empty cell 6. Cell C9 =IF(D9+E9=2,”a”,””).  Please ensure that cells D9:E9 are empty 7. Cell C10 =1/0.  This will yield the #DIV/0! error To count numbers […]

Read More →

Display text entries in the data area of a pivot table

{ 60 Comments }

Assume a two column database of approximately 20,000 rows (say from A1:B20237).  The first column has vendor names and the second column has Part codes (alphanumeric string).  Headings are in A1:B1, say Vendor in cell A1 and Part_code in cell B1.  One vendor supplies multiple parts and therefore there would be repetitions of vendor names […]

Read More →

Calculate turn around time excluding Sundays and public holidays

{ 206 Comments }

Assume a two column database showing starting date/time and ending data/time (Data/time stamp appear in a single cell).  Given a list of public holidays in a year and starting and ending work times, one may want to know the turn around time excluding Sundays and public holidays. You may refer to my solution in this […]

Read More →

Extract n th. most frequently occurring item from a database

{ 2 Comments }

Assume a database in range D5:D11 with the following values – Black, Black, Purple, Purple, Grey, Grey, Grey.  To extract the most frequently occurring item, one may use the following formula =INDEX($D$5:$D$11,MODE(MATCH($D$5:$D$11,$D$5:$D$11,0))) To extract the second most frequently occurring item, try the following: 1. Enter 2 in cell C16: 2. In cell D16, enter the […]

Read More →

Summarise data from multiple sheets with one condition – PartII

{ 2 Comments }

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 →