# Category Archives: Functions & Formulas

## Excel – Dealing with Non-Printable Characters

Every so often you may encounter a problem with Excel where a function is not working properly, even though you know that your function syntax is correct. And when you manually check the data you find that the function is not returning the correct answer.

So what is going on?

This problem happened to me again recently and it took a little while for the penny to drop as it is fairly rare, but highly annoying when it does.

And it is down to something called “non-printable characters”. These pesky characters are invisible, which makes it doubly difficult to identify. These tend to crop up in reports that have been downloaded from other systems, which for reasons unknown, slip in characters of some sort that cannot be seen or identified on screen, but nevertheless cause mayhem with a number of functions.

As an example I’ll use the data which caused me the most recent problem.

The company I work for uses a web based database to manage courses and delegates and some information was extracted to generate a catalogue within a workbook (sample above). I wrote some VBA code to help users find courses either by name of ID number and in one instance, the dreaded end-debug screen kept on appearing.

The form I created allows users to enter partial names or IDs and search for information based on that partial string. When they entered the number 26, a message told them that two records had been found.

This was done using a **COUNTIF** function. However, there were in fact 6 records that contained the number 26.

So on the one hand the **COUNTIF** function was finding 2 records, but the loop in the code which populated an array in the memory was crashing as it found a third record that contained the number 26. The dynamic array was not big enough as the size was recalculated based on the findings of the **COUNTIF** function.

Initially I looked at formats – text vs. numbers for example.

I also looked for any spaces that might have caused an issue.

I manually added a **COUNTIF** function in the worksheet to double-check the findings of the function created in VBA but this gave the answer 2 as well.

**=COUNTIF(C:C,”*26*”)**

Finally, realising that this was an extract from a non-Excel source, I tried using the **CLEAN** function suspecting that there may be some non-printable characters in there somewhere. The **CLEAN** function removes all non-printable characters from text. The syntax is very simple:

**=CLEAN(cell reference)**

In a new column I wrote this formula and then copied all the “cleaned” text values and used **PASTE SPECIAL VALUES** over the original cells and retested the **COUNTIF** function.

…and hey presto…it found all 6 records.

So next time your functions are not working and seem incapable of identifying values in cells, try applying the **CLEAN** function to your data, it might just be the solution to your problem.

## Excel – Using INDEX & MATCH (2)

You’ve probably come across the **MIN** and **MAX** functions which give you the lowest and highest values, respectively, in a range of cells. This is fine as long as all you need to know is the lowest or highest figure, but what if you want to know *who* or *what* got the lowest/highest figure?

Let’s take a simple example;

So we have a list of people and the amounts they have spent. With a quick scan of the data you can see that Mrs Delores Turner had the lowest spend, and Mr Louie Moreno had the highest. This is easy because there is only a very small amount of data, but if there were hundreds or even thousands of lines it would not be so obvious.

By using the **MIN** and **MAX** functions I can quickly find the lowest and highest values using **MIN(B2:B11)** or **MAX(B2:B11)**, but it doesn’t tell me “who” spent these values.

So how can you get around this?

This is where **INDEX** and **MATCH** come in. If you are unfamiliar with these two functions, check my blog https://excelmate.wordpress.com/2014/12/04/excel-using-index-match-part-1 which will show you how they work. For the rest of this blog I will assume you have either read the blog or are already familiar with **INDEX** and **MATCH**.

To get the name of the person with the lowest/highest spend I need the following formula;

**=INDEX(A2:A11,MATCH(MIN(B2:B11),B2:B11,0)**

The **MATCH** part of the formula find finds the lowest value in cells **B2:B11**. It then uses this number and finds its position within the range. In this example the **MATCH** function will return the value 4 i.e. it is the 4^{th} value in the selected column of data. The index function then looks within range **A2:A11** and looks in the 4^{th} row – the value returned by **MATCH**. You don’t need to reference the column because there is only one column selected in the array portion of the **INDEX** function.

The find the person with the highest spend, the formula would be;

**=INDEX(A2:A11,MATCH(MAX(B2:B11),B2:B11,0)**

So as you can see **INDEX** and **MATCH**, although pretty useless on their own as we saw in the earlier blog, when combined they are a useful duet of functions to know and should be part of your “must know” functions in Excel.

## Excel – Using the Formula Auditing Tools

Once you’ve created a spreadsheet or you get one from someone else, one of the more painful things you end up doing at some point is trying to work out which cells feed into any of the formulas throughout the workbook or alternatively, which cells are dependent on any given cell(s).

Finding which cells feed into a single formula is easy, just by clicking on the formula cell which then highlights all the cells that feed into it.

However, this is very limited as you can only check out one formula at a time and there is no way of knowing if any of the cells that feed into your formula are in turn dependent on other cells for their values short of checking each and every cell which of course no sane person would do.

So how can you check this without spending hours looking at all the cells in your worksheet/workbook?

This is where the **FORMULA AUDITING** tools come in. On your **FORMULAS** tab you will find the **FORMULA AUDITING** group.

In this blog I am going to concentrate on **TRACE PRECEDENTS** and **TRACE DEPENDENTS**, **ERROR CHECKING** and will write separately about **EVALUATE FORMULA**.

I will briefly mention **SHOW FORMULAS** before starting on the main topics of this blog. As the name suggests, this will convert all cells with formulas on the current worksheet from displaying their values to displaying the formulas contained within them.

You can achieve the same effect using **CTRL + `** (on an English – UK QWERTY keyboard). It’s a quick way of viewing all formulas on a worksheet without the need to view each cell separately. Can’t say any more than that!

So how do you find out which cells feed from or into another cell?

Let’s start by finding out which cells supply a formula cell with its inputs. Click on any cell containing a formula and then click on **TRACE PRECEDENTS**.

Any cell(s) that provide data to your formula should now be highlighted. But do any cells feed those before they feed into the formula cell? Simply click the **TRACE PRECEDENTS** button again.

Keep on clicking until it can go no further i.e. you have reached the original source to all the data feeding into your formula.

Now that you’ve found out all the cells that feed into your formula, is the result of your formula used anywhere else. In other words, are cells **DEPENDENT** on this one?

Staying on the same formula cell, now click on **TRACE DEPENDENTS**.

As with **TRACE PRECEDENTS** if you continue to click the **TRACE DEPENDENTS** button you will eventually get to the final cell in the chain that is influenced, however remotely, by the currently selected cell.

There will be times when you check for **PRECEDENTS** or **DEPENDENTS** and you will see this;

This tells you that data from an external source is feeding into the cell. This can mean from another worksheet in the same workbook, or from another workbook altogether. Double click the dashed line and it will open the **GO TO** dialog box.

This will show you the source of the data and if you click on the reference and click on **OK**, it will take you the feeder cell if it’s in the same workbook, or if the other workbook is open at the time.

There comes a point where there are too many lines/arrows to follow. To remove any lines/arrows click on **REMOVE ARROWS** to remove all visible lines/arrows, or select **REMOVE PRECEDENT ARROWS** or **REMOVE DEPENDENT ARROWS** depending on what you want clear off screen.

Alternatively, you can trace precedent and dependent cells by using **CTRL + [**and **CTRL + ]** respectively which simply highlights any cell(s) that either precede of follow the currently highlighted cell.

If your formulas produce error messages such as **#DIV/0!** And you have eliminated syntax errors, it could be that one of the feeder cells somewhere in the chain of cells has an error. Use **ERROR CHECKING** to find and correct those errors.

Click on **TRACE ERROR** to show all cells feeding into the formula producing the error. This works just like **TRACE PRECEDENTS** showing any external links.

For more information select **ERROR CHECKING**. This will show you where the error is and what type of error it is. You then have access to a number of options such as **EDIT IN FORMULA BAR** or **IGNORE ERROR** in case the problem is simply due to incomplete or missing data which will correct itself once it is updated.

So next time you are not sure where a formula gets its inputs from, or you need to trace an error you can now use your **AUDITING TOOLS** to help you out a bit.

## Excel – Using INDEX & MATCH (Part 1)

If you are familiar with the **VLOOKUP** and **HLOOKUP** functions then you will know how useful they can be. There is however one drawback in that they only search for the look up value in the first column or row of a selected look up table. In the majority of cases this is fine, but there may be times when you need to look both to the right and left of the look up column.

This is where **INDEX** and **MATCH** functions come in. On their own, they are probably two of the most useless functions going – together they are extremely powerful and sometimes jokily referred to as VLOOKUP on steroids! But to understand how they work we need to take at look at each one in turn.

MATCH

The basic syntax for **MATCH** is:

**=MATCH(lookup_value,lookup_array, [match type])**

Which in plain English is…

**=MATCH(what value are you looking for, where are you trying to find it, do you want a near or exact match)**

Whereas **V/HLOOKUP** require a **TRUE** or **FALSE** (1 of 0) value in the final argument, **MATCH** has three options;

1 = nearest match under/less than the look up value

0 = exact match

-1 = nearest match over/greater than the look up value

**MATCH** basically returns the position (represented by a number) of the look up value in a single row or column.

If I am looking for “X” in a row of cells then my formula would be =MATCH( “X”,A1:F1,0)

My function would return the number 4 i.e. it is in the 4^{th} cell, reading from left to right in my selected cells. If looking in a single column of cells then it returns the position reading from top to bottom. Not the most useful bit of information you are ever likely to extract from a spreadsheet if the truth be told!

INDEX

There are two versions of **INDEX**, but the one I am looking at here is the basic one that only looks at one table. The syntax for **INDEX** is:

=INDEX(array, row_num,[column_number])

…or in plain English;

=INDEX(range to check, which row do you want to look in, which column do you want to look in)

Using **INDEX** on its own is not the most useful thing you can do in Excel. For example;

=INDEX(A1:D7,5,3) would return 32

I could have just as easily written =C5. So it looks like a very long winded way of referencing cells. However…when you combine **MATCH** and **INDEX** together you get a completely different beast.

I’ll use a table we use in class as it is good way of showing how it can be used.

As you can see, we have a number of towns/cities around the UK with distances between them. Rather than search myself for distances between two given locations, I want to be able to use a series of drop down lists to select locations and a formula to find the correct distance between them. So I need something dynamic to do this, and this is where **INDEX** and **MATCH** come into play.

I’m going to use a couple of drop down lists to make my choices easier rather than type out the names each time. I’ll assume you know how to create **VALIDATION** lists, if not please see my blog http://wp.me/p2EAVc-5b on how to set those up.

To break this down I will use the **MATCH** function on its own to return the row and column numbers for each town/city I choose from my drop down lists. First, calculate the row position;

**=MATCH(S19,A1:A17,0)**

In this example, I have chosen Southampton and the **MATCH** function returns the number 5.

Now for the column position;

**=MATCH(S20,A1:Q1,0)**

This time I’ve selected Nottingham and therefore returns the number 10.

Change the location in each of the drop down lists and see the values from each **MATCH** function change. So now you can see that **MATCH** is a dynamic function when linked to something like a **VALIDATION** list.

As we saw before, **INDEX** relies on row and column numbers to work. Normally, these would be manually entered and therefore fixed. As **MATCH** can be dynamic, it returns a number we can use to our advantage by embedding a **MATCH** function in the row and column arguments of **INDEX**.

Our new nested function will look like this;

**=INDEX(A1:Q17,MATCH(S19,A1:A17,0),MATCH(S20,A1:Q1,0))**

Although it looks complicated, and may take a while to get used to the nested syntax, it is basically an **INDEX** function with two **MATCH** functions sitting inside it.

This now gives you a totally dynamic search function. Although in this example the look up values are the top row and first column, **INDEX & MATCH** will work using the look up on any column or row in your table.

What’s really neat, is that if you decide to move the town names around a bit (sticking to the same column or row mind you!) it will still work because the **MATCH** function will look for an exact match of the text value chosen in the drop down. Unless the spelling changes, it will find it wherever it is in the row or column!

In Part 2 I’ll show you how you can use **INDEX** and **MATCH** across multiple tables as well as extracting information from one column based on data found in another.

## Excel – MIN & MAX vs. SMALL & LARGE Functions

The **MIN** and **MAX** functions are two of Excel’s basic functions to find the single lowest or highest value in a range of cells and they appear in the drop down list on the **AUTOSUM** button.

Many beginners often ask in training sessions when they would be likely to use these functions, but once you know them, it’s surprising how much use you can actually get out of them.

The other two functions, **SMALL** and **LARGE** also return lowest and highest values respectively but with the added option to find the 2^{nd }, 3^{rd}, 4^{th}, 5^{th }lowest/highest ….etc.

The basic syntax for **MIN** and **MAX** is;

**=MIN(range to test)**

**=MAX(range to test)**

If we look at a basic table with a range of values…

You might be able identify the lowest or highest values as this is only a small number of cells, but try doing that with 1,000s or more – it would be very easy to get the wrong answer.

In this example, to find the lowest *single* value our formula would be;

**=MIN(A2:C15)** giving us the answer 45.

To get the highest;

**=MAX(A2:C15)** giving us the answer 975.

Simple, quick…enough said.

Now let’s take a look at **SMALL** and **LARGE**.

The basic syntax for these is;

**=SMALL(range to test, n ^{th} value)**

**=LARGE(range to test, n ^{th} value)**

Returning to our data table above, we could extract the following lowest values;

**=SMALL(A2:C15,1)**

giving us the answer 45…exactly the same as **MIN** i.e. lowest value

**=SMALL(A2:C15,3)** giving us the answer 124 (3^{rd} lowest value)

**=SMALL(A2:C15,12)** giving us the answer 239 (12^{th} lowest value)

And to get the largest values;

**=LARGE(A2:C15,1)**

giving us the answer 975…exactly the same as **MAX** i.e. highest value.

**=LARGE(A2:C15,5)** giving us the answer 863 (5^{th} highest value)

**=LARGE(A2:C15,9)** giving us the answer 787 (9^{th} highest value)

So there you have finding lowest and highest values. As with all Excel functions, combining these with other functions will give you a lot more power to analyse and find values in your spreadsheets. Look out also for my blogs on **DATE** and **TEXT** functions.

## Excel – Create a Dynamic 12 Month Rolling Chart

If like many people producing reports at work you report on a rolling yearly basis, you are probably manually changing the data range of your chart every month too either by changing the cell references or even worse, deleting the first month of data and adding the most recent data to the end of your table. All time consuming, and ultimately unnecessary.

It is possible to create a dynamic 12 month rolling chart that automatically displays the last 12 months of data (or any other time frame in fact). All you have to do is add data to the end of your data table and let Excel do the rest!

For this you will need to use the **OFFSET** function. If you are not familiar with this function, then go to my “Creating Dynamic Ranges” blog ( http://wp.me/p2EAVc-9Y ) to understand how this function works before tackling this…otherwise this will be a complete mystery and quite unfathomable.

Let’s start with some basic data – one year of sales figures.

To create a dynamic chart using this simple table we will need two named dynamic ranges – one for the data itself and one for the labels. Note that when working with charts you will need to create a separate dynamic range for each series as charts treat each series separately so you cannot create a single dynamic named range that includes all rows and columns.

Personally, I like to start by creating the dynamic range that handles the data.

Go to **NAME MANAGER** and select **NEW**, or go to **DEFINE NAME**. Give the range a name of some sort. In this example I will use **ChtData**. Don’t use the word Chart in your name, apparently it won’t work (Mr Excel).

In the refers to box enter the following formula;

We use **-12** in the **HEIGHT** argument as we always want to count back 12 rows from the last cell containing data.

Then we need a dynamic named range to pick out the correct labels (in this case dates) to match the dynamically selected data. In this example let’s call it ChtLabels.

Following the same steps as above enter the following formula;

Rather than work everything out from another reference cell, we can use our **ChtData** range as the reference point or in this case reference range. This will work out what to select based on the first dynamic range, and select values from one column to the left (**WIDTH** argument = **-1**).

Check that both ranges work properly by adding some new data at the bottom of the chart and click into the refers to box of the named range to see which cells it selects.

Now time to create our chart. Just follow the usual steps to create a chart…**INSERT**, **CHART** etc.

Once our “static” chart is set up, now comes the clever bit.

Make sure you have clicked on your chart and then click on **SELECT DATA**.

In the **SELECT DATA SOURCE** window, click on the series you want to turn into a dynamic one. If you have multiple series, then you will need to do the following steps for each one.

Click on **EDIT**.

Currently you should see the cell references relating to the series in the **SERIES VALUES** box. Remove any cell references but ** leave** the sheet name and exclamation mark. Replace the cell references with the dynamic range name for the data – in this example

**ChtData**. Click on

**OK**. Now do the same for the labels.

Click on any one of the labels under **HORIZONTAL (CATEGORY) AXIS LABELS** and then click on **EDIT**.

As before, remove any cell references from **AXIS LABEL RANGE**, leaving the sheet name and exclamation mark exactly as before. So in this example we should now have **Book1!ChtLabels**.

All ready to go! Now just add new data and watch your chart automatically update to always show the last 12 months of data.

## Excel – Creating Dynamic Named Ranges

If you use named ranges in Excel, you’ll know how useful they can be. If you are not familiar with them check my blog ( http://wp.me/p2EAVc-99 ) on setting up named ranges and how they can be used.

If you are currently using them you will also know that it can be a bit of pain having to update the range all the time as new records are added or removed from your data. If your data does change regularly, wouldn’t it be nice to have a named range that automatically adjusts to the correct number of rows or columns? Well…you can, by creating dynamic ranges.

At the heart of a dynamic range is the **OFFSET** function. Before we embark on creating ranges, let’s look at the syntax first as it is not that straightforward.

The official syntax you get from Microsoft when you enter the function is;

**=OFFSET(reference, rows, columns, [height], [width])**

…which doesn’t really tell you much if you have never used this function before.

**Reference**: every range has a starting point, even dynamic ones. Reference is like an anchor point from which the rest of the range is referenced.

**Rows**: is the number of rows away from the reference or anchor point. Positive numbers represent rows down, and negative numbers rows up.

**Columns**: is the number of columns away from the reference or anchor point. Positive numbers represent columns to the right, and negative numbers columns to the left.

=OFFSET($A$1,0,0 would mean the range starts from A1

=OFFSET($A$1,1,0 would mean the range starts from A2 (1 row below)

=OFFSET$A$1,0,1 would mean the range starts from B1 (1 column to the right)

…and so on and so forth.

**Height**: this is optional, but represents the number of rows you want to include in your range. So if we had =OFFSET($A$1,0,0,5 then the range would be 5 rows starting from A1.

**Width**: another optional argument. This sets the number of columns that make up your range. Continuing the formula from above, if we have =OFFSET($A$1,0,0,5,3) then we would set our range to 5 rows high and 3 columns wide starting at A1.

This of course gives us a fixed range, as we are specifically defining the number of rows and columns we want in the named range.

So how do we get it to be dynamic?

We need to build in functions that calculate the number of rows and/or columns within the **OFFSET** function. Generally speaking we do this within the **height** and **width** parts of the function, but you may need to alter the other arguments such as when you work with dynamic charts.

So let’s build a dynamic range;

First of all, you can’t create a dynamic named range in the same way as a normal named range i.e. select a bunch of cells and give them a name. We need to go to the **NAME MANAGER** and click on **NEW**, or go to **DEFINE NAME**.

In either case, give your range a name.

Decide whether the range is specific to the worksheet or can be referenced from anywhere in the workbook.

Add a comment…purely optional as a note to yourself or anyone else vaguely interested in the named range.

And then the important bit – **REFERS TO:** Rather than entering a basic range, this is where we have to use the **OFFSET** function.

Starting with a basic table, I want to create a dynamic range that automatically works out the number of rows in a fixed width table of 5 columns.

So our formula will look like this;

**=OFFSET(Sheet1!$A$1,1,0,COUNT(Sheet1!$A:$A),5)**

In plain English…starting 1 row below A1, count the number of cells that contain numbers in column A (to work out number of rows) and set the width to 5 columns.

In this example I am excluding the heading row, but you may well need to include it, if for example, you are creating a dynamic range to use in a Pivot table.

Below, we can see the correctly selected data.

If I add a few rows of data, the range is automatically recalculated to include them.

If I want the range to check the number of columns too then I need to modify the formula as follows;

**=OFFSET(Sheet1!$A$1,1,0,COUNT(Sheet1!$A:$A), COUNTA(Sheet1!$1:$1))**

Now, not only does the formula count the number of cells containing numbers in column A, it counts the number cells containing text (hence COUNTA)across the top of my data table to give me the number of columns that make up my table.

So now you can create either a one-way or two-way dynamic range depending on your personal requirements, and no need to manually update the **REFERS TO** cell references.

In another blog, I will show you how you can use the **OFFSET** function to create dynamic charts.

## Excel – Logical Functions

Logical functions are probably one of the most commonly used in Excel. You’d be hard pressed to find any semi-decent spreadsheet without an IF statement in it somewhere.

If you have never used a logical function or argument before, it’s basically one that has two possible outcomes – true or false or in computer speak 1 or 0.

In this blog I am going to show you how to use the following logical functions;

- IF
- AND
- OR
- XOR (2013)
- IFERROR
- IFNA

**IF**

This is the main one. It can be a simple TRUE/FALSE statement, or can be nested several times to perform multiple sequential tests, each one giving a TRUE/FALSE outcome. The basic syntax for an IF statement is:

**=IF(logical test, do if true, do if false)**

Examples of logical tests might be;

A5 = 100

D4>=25

G23=F23

E10 = “Excelmate”

If the test is **TRUE** then the “do if true” part of the statement is executed. This could be a calculation, a look up, display a text string…pretty much anything you can create using functions or adding text. If the outcome to the test is **FALSE**, then it will execute the “do if false” bit of the statement. Again, you decide what happens here. Note that the **FALSE** bit is optional, so you don’t need to enter anything here. However, if the outcome is **FALSE**, and you have opted to leave this blank in your formula, Excel will display a not particularly helpful **FALSE** in your cell. If you want to display an empty cell simply put “” (no space between the speech marks) in the **FALSE** part.

A few basic examples;

A basic IF statement is fine and dandy, but you will probably want to perform a combination of tests. This is where **AND** and **OR** come in handy.

**AND & OR**

More often than not, you will find these nested in an **IF** statement. You can use them on their own to get a **TRUE** or **FALSE** outcome, but personally I have not found any real use for them in that way.

Both **AND** & **OR** can each perform up to 255 tests;

**=AND(test1, test2, test3,…test255)**

**=OR(test1, test2, test3,…test255)**

In order for **AND** to return **TRUE**, **ALL** tests must be **TRUE**. If just one is **FALSE** then the whole statement becomes **FALSE**.

In the case of **OR**, as long as one test is **TRUE**, the whole statement returns a **TRUE** value. To return a **FALSE** result **ALL** tests must be **FALSE**.

A common mistake I see is people mix up **AND** & **OR**. In their heads they think **AND** when logically they mean **OR** and vice-versa. You will soon see if you get some odd results when you run your formula!

What is initially illogical is how to nest **AND** & **OR** within an **IF** statement;

**=IF(AND(test1,test2,test3),do if true, do if false)**

**=IF(OR(test1,test2,test3),do if true, do if false)**

In your head you are probably saying “if test1 = x, and test2 = y, and…” which tempts you in to writing it as you think it…but don’t.

Here’s a few examples to see what they give:

Or if using OR…

**IFERROR & IFNA**

In earlier versions of Excel if you wanted to check if a function returned an error you had to nest an **IS** function (**ISERROR**, **ISNA**, **ISREF** etc.) within and **IF** statement. As these are such common tests they have introduced these two functions that combine the logical **IF** and the logical **IS** type function. These are useful for example, to keep your cells tidy rather than getting a load of error messages all over the place.

A few examples for you;

In this example, the formula does a VLOOKUP and if it returns and error, it will display nothing rather than the usual #N/A error message. I could have used IFNA in this case as the VLOOKUP returns that type of error bur **IFERROR** is a good catch all for all types of errors.

**XOR**

This has been around in other programming languages for years but never featured in Excel until they brought out 2013.

If you have never come across this function before it is a little unusual. Whereas **AND** returns **TRUE** if **ALL** tests are true, and **OR** will return **TRUE** if any one or more tests are found to be true, **XOR** (e**X**clusive **OR**…get it now?) will behave in the following way;

2 logical tests: if A is **TRUE** and B is **TRUE** then result = **FALSE**

If A is **TRUE** and B is **FALSE** then result = **TRUE**

If **both** A and B are **FALSE** then result = **FALSE**

However…if there are 3 or more tests, then things get interesting;

TRUE – TRUE – TRUE = TRUE

TRUE – TRUE – FALSE = FALSE

TRUE – FALSE – TRUE = FALSE

FALSE – TRUE – TRUE = FALSE

FALSE – FALSE – TRUE = TRUE

FALSE – FALSE – FALSE = FALSE

Have you worked it out yet?

In the case of 2 logical tests, the **XOR** function basically returns **TRUE** if the result of **EITHER** test is **TRUE**, but not both and not neither i.e. both are **FALSE**.

In the case of three or more logical tests, **XOR** returns **TRUE** if the number of **TRUE**s is **ODD**, and will return **FALSE** if the number is even. Obvious!

Personally I have never had a need for such a function but perhaps one day it might come in handy. If you find a good way of using it then fell free to post a comment on this site.

So there you have some logical functions. As with all functions in Excel, you really get to see their real power by nesting functions together. The IF statement alone can be nested up to 64 times in a single statement. Having said that there is probably something seriously wrong with your spreadsheet if you need to create something that long-winded, and should probably be looking to use VBA to do the work. Putting that aside however, logical functions are probably one of the most commonly used functions in Excel after autosum and are an essential part of your spreadsheet functions arsenal.

## Excel – Counting Functions

Ever sat at your desk holding a pen to the screen counting cells? I know I have in the past. Well…no need, Excel has a bunch of counting functions to help you out.

In this blog I am going to look at five counting functions:

- COUNT
- COUNTA
- COUNTBLANK
- COUNTIF
- COUNTIFS

COUNT

The most basic of the counting functions. You can find this in the drop down list next to the **AUTOSUM** button.

Somewhat helpfully, it shows **COUNT NUMBERS** which is exactly what **COUNT** does. It only counts cells that contain numbers and ignores text cells. Alternatively, just type =**COUNT(** and select the cells you want to count. In the screenshot below you can see what **COUNT** returns depending on the contents of the selected cells.

COUNTA

This is the counterpart to **COUNT**. This will count cells that contain anything – text or numbers. If you want to count cells that only contain text then you have to use COUNTA otherwise you get a count of 0. Again, the screenshot below shows you the various results based on the contents of the cells

COUNTBLANK

Does exactly what is says…counts blank cells. Can’t say any more than that really. Note however, that if a cell contains a space (typed by accident probably) it will ignore it as the cell is no longer “blank” as it contains a space character.

COUNTIF

Slightly more complex than the previous three but very useful. The **COUNTIF** function counts how many times a specific value (numerical or text) appears in a range of cells. For example you might be organising an event and need to get people’s choice for main course at the evening dinner. Let’s say there is a choice between fish, beef, chicken and vegetarian. You need to know how many people have opted for each of the mains – this is where **COUNTIF** can help.

The syntax for COUNTIF is as follows:

**=COUNTIF(range of cells to check, what to count)**

If you are counting text values then the “what to count” bit must be in double speech marks i.e. “fish”.

If you want to count how many times a number appears then no need for any speech marks. However, if you are counting how many numbers are greater than, less than etc. , you need to put this in speech marks too (this often catches people out and is not really logical). For example I want to count numbers greater than 10, my formula would read:

**=COUNTIF (A1:A20,“>10”)**

A few examples below:

COUNTIFS

Whereas **COUNTIF** checks a single range of cells, **COUNTIFS** allows you check multiple ranges each with different criteria. In order to count, all criteria must be met in all of the selected ranges. You can create up to 127 different range/criteria combinations.

The syntax for **COUNTIFS** is as follows:

**=COUNTIFS(range1 to check,range1 criteria, range2 to check, range2 criteria….)**

Using our example of the menu choices, let’s extend our choices to include wine. So people can choose a main and a choice of red, white or rose. Our function will then have to count people who want fish and white wine, fish and rose or (perish the thought) fish and red wine, and then the same for each of the other mains.

So there you have a selection of counting functions. Never again will you need your biro to point to cells on screen whilst trying to scroll down at the same time!

Check out my other blogs on functions – date and text functions.