Tags: COUNTIF

Programmatically transfer data from master sheet to sub sheets with conditions

{ 45 Comments }

A user inputs raw material purchase data in a master sheet.  This data is entered by date and vendor.  One may want to view individual vendor sheets at a monthly level.  In the attached file, i have presented two solutions – a macro based one and a formula based one. The data layout and instructions […]

Read More →

List down most frequently appearing names in descending order of frequency

{ 0 Comments }

Given a list of names in a worksheet range, one may want to pull out, the most frequently appearing names in descending order of frequency, to another range.  Furthermore, one may want to provide an additional date criteria as well. 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 →

Reduce scrolling in data validation

{ 4 Comments }

Given a large number of entries in the source of data validation, it may become cumbersome to scroll down the list to select a desired value.  It would be ideal to have the user type the first alphabet and be taken to all words which start with that alphabet. The drop down list in cell […]

Read More →

Return closest numeric match

{ 2 Comments }

In range A2:A6, the numbers are 7.5, 2, -12, 11.2 and 8.  In cell B8, a user enters 8.  One may want to answer the following questions: 1. Return the closest number which is less than or equal to the number in cell B8 2. Return the closest number which is greater than or equal to the […]

Read More →

Count unique values with conditions

{ 13 Comments }

There is a three column database with month in column A, numbers in column B and names in column C.  I want to count unique numbers in column B for a specific month and name provided by the end user. Depending upon the version of MS Excel which you are using, there could be two […]

Read More →