# Tags: COUNTIF

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]