formula friday 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

Month To Year Calculator In Excel.

August 20, 2021 by Barbara

Hello Excellers, Welcome to another blog post in my #FormulaFriday #ExcelTips series. Today I will show you how to create a Month to Year calculator in Excel. I have a sample file below to work through. This extract is from a Human Resources database that exports the length of service in months. I would like to see this in Years and Months for convenience.

So, for the first record in the database, Julie Troper has a service value of 56 months. It is much more convenient to view the length of service in Years and Months, not just months.

So, let’s insert a new column into the data set and calculate the Months into Years. The result will look like this screen grab below.

The Solution. Creating A Month To Year Calculator In Excel.

The solution uses the INT function and the MOD function. Let’s work though the calculation one step at a time.

The INT Function.

The Excel INT function returns the integer part of a decimal number by rounding it down to the integer. In the first part of this solution, the fifty-six months is divided by twelve months in the year to convert the months into years. So, Julie Troper will have served four years. Therefore, four years is the result of fifty-six months divided by twelve rounded down..

The MOD Function.

The second Excel function used in this solution is the MOD function which returns the remainder of two numbers after division. We already have the INT Function calculation for how many years, and this function will cover the remaining months. Right on!.

Putting It All Together.

The rest of the formula simply adds in the text Years and Months. How cool is that right?. Use “&” to join the results of INT and MOD to get the final result. You can copy the formula below if you need to. Just adjust the cell references for your Excel worksheet.

=INT(C3/12) & ” years and ” &MOD(C3,12)& ” months”

Find The First And Last Occurrence Of A Value With MINIFS and MAXIFS Functions.

August 13, 2021 by Barbara

Hello, Excellers. Today’s blog post will show you how to find the first and last occurrence of a value. I recently used this method to query the first and last dates that a product appeared in my data set. Or, for what period of time was a product actively selling?. We can quickly achieve this by using the Functions MAXIFS and MINIFS. Both of these are two of the new Functions available in Excel 2016.

Table Of Contents.

The Question To Be Answered!. Data Scenario.
- MINIFS Syntax.
  - MINIFS Explanation.
- MAXIFS Syntax.
  - MINIFS Explanation.

The Question To Be Answered!. Data Scenario.

So, let’s suppose my data set looks like this extract below. I have a list of products along with the Week Ending Sales Volumes Data. I want to see the first and last dates that some of my products appeared in the data.

So it is really easy to get the earliest and latest dates for any of our products with the MINIFS and MAXIFS functions.

MINIFS Syntax.

MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Where

min_range is a required argument. A range of cells that Excel will use with the specified criteria.

Criteria_range1 this also required and is the set of cells to evaluate with the criteria.

We will use this MINIFS function in our example to find the earliest date one of our products appeared in our sales data. We need to write the following formula to return the relevant value.

MINIFS Explanation.

=MINIFS(B3:B20,C3:C20,F3)

Where min_range is our list of dates, criteria_range1 is our list of products, and F3 contains the product to search. For example, we have correctly calculated that Jamie Doll first appeared in our data Week Ending 26/05/2017. We have correctly calculated that Jamie Doll first appeared in our data Week Ending 26/05/2017.

We can repeat the process of MINIFS with MAXIFS to find out the last week that our product Jamie Doll appeared in our data.

MAXIFS Syntax.

MAXIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Where

max_range this is a required argument and contains the actual range of cells in which the minimum value will be determined

criteria_range1 this also required and is the set of cells to evaluate with the criteria.

criteria1 this is also required and is the criteria in the form of a number, expression, or text that defines which cells will be evaluated as a minimum.

criteria_range2, criteria2 further arguments are optional. These represent additional ranges and their associated criteria. You can enter up to 126 range/criteria pairs.

MINIFS Explanation.

=MAXIFS(B3:B20,C3:C20,F3)

So, max_range is our list of dates, criteria_range1 is our list of products, and F3 contains the product we need to find the last date it appeared. For example, Excel has calculated Jamie Doll last appeared in our data Week Ending 25/06/2017.

That’s how simple it is. How to find the first and last date a product appeared in the sales data.

Do you want even more Excel Tips?

Do you want more Excel Tips?.

Why Don’t You Use File | Save As?

Introducing the CELL Function.

More Excel Tips.

The Solution. Creating A Month To Year Calculator In Excel.

The INT Function.

The MOD Function.

Putting It All Together.

Table Of Contents.

The Question To Be Answered!. Data Scenario.

MINIFS Syntax.

MINIFS Explanation.

MAXIFS Syntax.

MINIFS Explanation.

Tags

Recommended Excel Resources

SHARE

Additional Info