Monthly Archives: April 2013

Array formulas – The basics

Looking up data from tables is very important when creating dashboards or for that matter even when crunching data for any analysis. While there are already some lookup formulas, that you know of and we have covered in our Dashboard 101 tutorial as well, not all of these formulas are capable of giving you the desired result.

For example let’s look at the data table below. It contains the data for drug samples given by sales reps to physicians. In order from left to right the columns are 1) the visiting reps’ name, 2) the visited physicians’ name, 3) the date of visit and 4) the number of samples given.

Array formula example table

Now if you were to find out, in a single formula, the total number of samples left by reps containing ‘Andrew’ in their name and visit date between 1st Jan and 15th Jan 2012 (both inclusive)– how would you do that?

Well… the answer is array formula. Continue reading