Excel Formulas Archives

Formula Friday – How To Count Cells That Contain Text Using COUNTIF

October 15, 2021 by Barbara

Hello Excellers, welcome to another #Exceltips blog post in my 2021 series. recently I have been counting cells with text in my Excel worksheet. So, I thought it would be a great Excel tip to share.

So, do you need to count how many cells that contain text?. I have used this technique to how many rows in my Excel data set contain any text. For example, the text references in my data represent a particular type of customer, which I can easily count with the formula COUNTIF and a wildcard!

So, here is a speedy way to count how many of your cells contain text. (That is not numbers, not errors, not blanks).

Let’s work through an extract of my data set.

counting cells that contain text in Excel

I want to see how many of my customers have text in their customer_id. The syntax of the COUNTIF formula is

=COUNTIF(range, criteria)

Where

range= the range of cells from which you want to count non blank cells

criteria = the condition, i.e a number, expression or text that determines which cells should be counted.

We can use the wildcard * as the criteria argument in our formula, to search for ANY text in our customer_id data range

The wildcard is surrounded by quotation marks (” “).

Excel has correctly identified that I have 2 customers in my list that contain some type of text characters. Job Done!. Easy as that. A simple way of counting cells with text in Excel.

Do you want even more Excel Tips?

Add Emojis To Your Excel Formulas. Excel Tip.

October 8, 2021 by Barbara

Hello, Excellers. Welcome back to another #FormulaFriday Excel tip. Today I will show you how to add emojis to your Excel formulas. Yes, emojis are everywhere. Even in Excel formulas if you want them to be there. So if emojis are your thing, follow the instructions below to use them in your Excel formulas. Let’s take an example from start to finish.

My data set is below. Let’s use the 👍 emoji if sales are on or ahead of target. Alternatively, we can display the 👎 emoji. The result should be like the data in the screengrab below. Emojis are shown as black and white in Excel on desktop, but Excel online is full colour.

The IF Formula is used to create this result. The syntax of the IF function is below if you need a refresher.

=IF (logical_test, [value_if_true], [value_if_false])

So, in this example, the logical test is if the Actual Sales are greater than or equal to Sales Target. If the logical test is true (or met), then insert the 👍emoji. If the test is false,e then 👎 will be inserted into the cell.

So, to insert the emojis, hit the Windows key and the period key (.) or the semi-colon key (;). Then, select the emoji you need.

The full formula looks like the screen- grab below.

Follow the steps below to write the formula. To access the Emoji hit the Windows plus period or full stop, or semi colon.

Do you want more Excel Tips?.

Finally. if you like free Excel tips then why not sign up for my monthly newleteer. I share three FREE Excel tips direct to your email box. No spam. I also share some great Excel course discounts when I can. I hope you can join thousands of other Excellers in this great community. See you over there.

Can’t Remember Where Your File Is Saved?. Cell Function To The Rescue

September 24, 2021 by Barbara

Hello and its #formulafriday again. Today, let’s look at a really interesting way to find out where you have saved your Hello, Excellers, welcome to another #formulafriday blog post in my 2021 Excel. Today, let’s look at a simple way to find out where you have saved your file. If you cannot remember the exact file path, using the CELL function in Excel will solve this problem.

So, did you know that this is one of the FREE tips I shared directly with my subscribers’ inboxes every month?. So, why not sign up for my Newsletter and get fresh Excel tips every month?

Why Don’t You Use File | Save As?

Well, yes, you probably are saying I could use the File | Save As process to save the file again. Yes, that is correct, but using the CELL function on your worksheet can act as a permanent reminder on your Excel worksheet of the file save location you have chosen for your Excel file. So, use the CELL function as a once-off or have a reminder at the top of your Excel worksheet.

Introducing the CELL Function.

The CELL function returns information about a cell’s formatting, location, or contents, and its syntax is straightforward.

=CELL(info_type, [reference])

Where

info_type – this is a text value that specifies what type of cell information is required. This is a required argument. The list below shows the possible values of the argument and corresponding results.

reference – the cell that you want information about. This is optional. If left out, the information specified in the info_type argument is returned for the last cell that was changed. If the reference argument is a range of cells, the CELL function returns the information for only the upper left cell of the range.

In this example today, I will use the “filename” value highlighted in the above graphic. Excel returns the full file path of the current workbook as text in the cell that contains the function. Empty text is returned if the file has not been saved previously.

So, let’s take a look at an example with my current file. The first argument of “filename” is used, returning the full file path. The name is New Worksheet, and I am currently working on Sheet4. How easy was that?.

If you want more Excel and VBA tips, then sign up for my FREE monthly newsletter where I share three free Excel tips. When you sign up you will get my FREE Top 30 Excel Tips E Book.

More Excel Tips.

Finally, don’t forget to bookmark my Macro Mondays and Formula Friday pages. They contain all of my next Excel tips and are updated every week.

Learn Excel With Our Excel Online Courses

Count Cells That Contain Text. Excel Tip

September 17, 2021 by Barbara

Hello Excecllers. Welcome back to another #ExcelTips blog post in my 2021 series. This Excel tip tutorial will demonstrate how to count cells in your Excel selected range that contain text.

Using The COUNTIF Solution To Count Cells That Contain Text.

So, the first of two solutions is to use the COUNTIF formula or function. Just a quick recap for those who have not used COUNTIF before. It is one of Excel’s Statistical Functions and enables a count of the number of cells that meet a criterion. In this example, it is to count how many cells contain text.

=COUNTIF(range, criteria)

Where

range = The range of cells that you want to count based on the criteria.
criteria =The criteria used to determine which cells to count.

Let’s start the function. In this example, the range is cells B3:B6. So, the criteria we are using is the WILDCARD. This Let’s start the function. In this example, the range is cells B3:B6. The criteria we are using is the WILDCARD. WILDCARD is a special character that represents one or more unknown characters. The most widely used wildcard character is the asterisk (*). An example is below, which will be the basis for the function to count cells with text.

Tr* – this will match any words that begin with Tr, such as Train, Tracks or Trooper.

wild card in Excel . count cells with text

Work Through A COUNTIF Example.

YSo, yu get the idea right?. So, putting the full formula together it looks like this. By just using the wildcard itself any cells that contains text is counted towards to final COUNTIF calculations.

=COUNTIF(B3:B6,”*”)

Excel returns the correct result of 3 cells in the range that contain text. Note that the wildcard is not case sensitive and will not be affected by the text’s upper and lower case variations. That is how to use the COUNTIF function to count the number of cells that contain text.

So, if you enjoyed this Excel tip why not sign up for the How To Excel at Excel Newsletter. You will receive three FREE Excel tips every month direct your inbox. I also have some great offers on my personally recommended Excel courses. Why not join thousands of other Excellers getting better at Excel?. Use the link below. No spam.

Here are some of my personally recommended Courses.

Finally Do You Want Even More Excel Tips?.

Unstack Excel Data (like Addresses).

September 10, 2021 by Barbara

Hello Excellers, welcoe back to another #ExcelTips, #FormulaFriday in my Excel 2021 series. Today I will show you how to unstack data in Excel that looks like the sample data set below WITHOUT Formulas!. I have inherited an Excel worksheet that contains address data. Unfortunately, each address line stacked on top of another makes any data analysis or use more difficult.

The data set is a list of addresses with three lines, Street, Town and Code. The address lines are not in columns but rows. Not a problem. A few clicks can sort this out. So, follow the steps below to begin filling out the new data set.

Point the cursor to G4 for the Street
I5 for the Town
J6 for the Code

Select the first three lines of the data set and drag the formula down to the bottom of your data set.
Hit Ctrl+C
Paste | Paste Special Values

With the data set still selected, press F5
Select GoTo | Special
Next select Blanks. All of the blank cells are highlighted
Right click | Delete | Shift Cells Up

The data moves up and aligns correctly.

So, that is how easy is it to unstack your Excel data set. The address data can now be used as a data table or database records that are more useful and easier to analyse.

Do you want even more Excel tips?. How about reading all of my Macro Monday or Formula Friday blog posts?. Why not bookmark them. They are updated every week.

1
2
3
…
42
Next Page »