Shahriar Abrar Rafid

Hello AHMED
I think you made an unintentional mistake during the calculation.
After 5 years, the future value would be $243.97. The result would come the same whether you calculate manually or use any function.
Calculating manually, you’ll get the result below.

And, using the FV function the result would come like in the one below.

Hello Elaine,
First, it feels good to see your interest in learning it. That motivates us to work hard.
Now, coming back to your question. Here you intend to enter the End Date Status automatically. Yes, that’s a very good thought. In this era of automation, why should we lag behind? Hahahaha…. Okay, let’s get to the solution now.
There could be 4 separate conditions. First, the Last Follow-up Date could be before the End Date of the study and the patient is dead at the Last Follow-up Date. Second, the Last Follow-up Date could be after the End Date of the study and the patient is dead at the Last Follow-up Date. Third, the Last Follow-up Date could be before the End Date of the study and the patient is alive at the Last Follow-up Date. And the fourth, the Last Follow-up Date could be after the End Date of the study and the patient is alive at the Last Follow-up Date. So, how can we determine the End Date Status for these situations? Let’s see the steps below.
• Firstly, go to cell G5 and enter the following formula.
=IFS(AND(E5=1,D5<F5),1,AND(E5=1,D5>F5),0,AND(E5=0,D5<F5),"?",AND(E5=0,D5>F5),0)
Here, we used the IFS function where we inserted those 4 conditions as logical_test and their output as value_if_true. Further, we used the AND function to join two conditions together.
• Finally, press ENTER.

That’s all from me on this. Don’t forget to visit our website, Exceldemy, a one-stop Excel solution provider, to explore more.
Stay well and healthy. Happy Excelling ☕.

Hello Emily,
It is very motivating for us when someone benefits from using our method. Now getting back to your query. You can use a simple copy-paste feature to overcome this issue. Let’s see the process below for a better understanding.
• Firstly, select cells in the C5:C13 range.
• Then, press the CTRL key followed by the C key on the keyboard.

This command copies the whole range.
• After that, right-click on cell E5.
• In the context menu, select Values (V) in the Paste Options.

You can see the data pasted in the new place.

• Additionally, select cells in the B5:B13 range and C4:C13 range also.
• Therefore, delete them using the Delete button.

• At this time, press CTRL+C to copy the cells in the D5:D13 range.

• Henceforth, go to cell B5 and paste them by pressing CTRL+V.

Finally, you get the desired result.

Hello NOOB Excel,
Thanks for your feedback and sorry for the inconvenience. Now, check the article. We’ve updated it according to your input. Look over it and let us know if it works well for you now.

Regards,
SHAHRIAR ABRAR RAFID
Team ExcelDemy

Hello GREG,
Thanks for your comment. Yes, you can do it. Here, I’m showing you to do it with Bing Maps API. Because it’s free. To use Google Maps API, you need to register with Card information. So, I have chosen Bing over Google here.
The following VBA macro is the one-stop solution to your problem.

Sub PostalCodeFromLatLong()
    Dim selectedRange As Range
    Dim latitudeColumn As Range
    Dim longitudeColumn As Range
    Dim postalCodeColumn As Range
    Dim apiKey As String
    Dim requestUrl As String
    Dim xmlhttp As Object
    Dim responseText As String
    Dim postalCode As String
    Dim i As Integer
    
    ' Select the range containing latitude and longitude coordinates
    On Error Resume Next
    Set selectedRange = Application.InputBox("Select the range containing latitude and longitude coordinates:", "ExcelDemy", Type:=8)
    On Error GoTo 0
    
    ' Check if a range is selected
    If selectedRange Is Nothing Then
        MsgBox "No range selected. Macro will exit.", vbExclamation, "ExcelDemy"
        Exit Sub
    End If
    
    ' Columns with latitude, longitude, and postal codes
    Set latitudeColumn = selectedRange.Columns(1)
    Set longitudeColumn = selectedRange.Columns(2)
    Set postalCodeColumn = selectedRange.Offset(0, 2).Resize(selectedRange.Rows.Count, 1)
    
    ' Bing Maps API Key
    apiKey = "Paste_your_Bing_Map_API_key_here"
    
    ' Loop through each row in the range
    For i = 1 To selectedRange.Rows.Count
        ' Get latitude and longitude values for the current row
        Dim latitude As Double
        Dim longitude As Double
        latitude = latitudeColumn.Cells(i).Value
        longitude = longitudeColumn.Cells(i).Value
        
        ' Construct the request URL
        requestUrl = "https://dev.virtualearth.net/REST/v1/Locations/" & latitude & "," & longitude & "?key=" & apiKey
        
        ' Create a new XMLHTTP object
        Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
        
        ' Send a GET request to the API
        xmlhttp.Open "GET", requestUrl, False
        xmlhttp.send
        
        ' Retrieve the response text
        responseText = xmlhttp.responseText
        
        ' Extract the postal code from the response
        postalCode = GetPostalCodeFromResponse(responseText)
        
        ' Write the postal code to the adjacent cell in the postal code column
        postalCodeColumn.Cells(i).Value = postalCode
    Next i
    
    MsgBox "Postal codes retrieved successfully!", vbInformation
End Sub

Function GetPostalCodeFromResponse(responseText As String) As String
    Dim postalCodeStartIndex As Long
    Dim postalCodeEndIndex As Long
    Dim postalCode As String
    
    ' Find the starting position of the postal code
    postalCodeStartIndex = InStr(responseText, """postalCode"":""") + Len("""postalCode"":""")
    
    ' Find the ending position of the postal code
    postalCodeEndIndex = InStr(postalCodeStartIndex, responseText, """") - 1
    
    ' Extract the postal code from the response
    postalCode = Mid(responseText, postalCodeStartIndex, postalCodeEndIndex - postalCodeStartIndex + 1)
    
    ' Return the postal code as a string with leading zeros
    GetPostalCodeFromResponse = "'" & postalCode
End Function

Run this macro and it will ask to input the range containing the latitude and longitude. Make sure to keep a blank column adjacent to the input columns. You’ll get the output there.

Regards,
SHAHRIAR ABRAR RAFID
Team ExcelDemy

Hello JEREMY,
Thank you for your comment. I’ve understood your problem. You can use the following instruction. Don’t paste this code into the module. Use it in the sheet module.
Right-click on the sheet name and select the View Code option from the context menu.

Besides, you can double-click on the specific sheet to add a module for this sheet especially.

In the module, paste the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
    If Cell.Column = Range("C:C").Column Then
        If Cell.Value <> "" Then
            Cells(Cell.Row, "A").Value = Date
            Cells(Cell.Row, "B").Value = Time
        Else
            Cells(Cell.Row, "A").Value = ""
            Cells(Cell.Row, "B").Value = ""
        End If
    End If
Next Cell
End Sub

You don’t have to run this code. Simply, save it and return to the worksheet and it’ll work smoothly.
If you delete the data in Column C, the date and time for this particular data will be erased also. But, don’t delete the entire column, it’ll create an error and you have to press CTRL + X to stop the macro from running.
Again, thanks for your query. We always welcome our readers to ask this type of info-ful questions.

Regards,
SHAHRIAR ABRAR RAFID
Team ExcelDemy

Hello G,
I think that you want to write a name, then some spaces, and then a date in one cell in Excel. Am I right? If that’s the matter, the solution is quite easy.
At first, select the cell (e.g. cell B3) and write your desired name. In this case, we wrote G.

Then, press the SPACE button multiple times according to the space you need. Here, we gave 10 spaces to create an indentation.

Next, write the date like the following image.

Finally, press the ENTER key. And the ultimate outcome is as follows.

Look, this is quite easy. But, if you want to mean something else, please write specifically. It’ll be helpful for us.
Thanks for your comment.
Regards,
SHAHRIAR ABRAR RAFID
Team ExcelDemy

Hello TANJA,
Hope you are doing good. Thanks for your query.
specifically, which method’s formula isn’t working for you? If you said that, I could understand well. Because all formulas are working on my PC.
The error message “There is a problem with this formula” usually appears when there is an issue with the syntax of the formula. If we take Method 2 as example, the following is the formula we used in cell E5:

=MIN((DATEDIF(C5,TODAY(),"d")+1)/(DATEDIF(C5,D5,"d")+1),100%)

It’s possible that the issue is related to the regional settings or the version of Excel being used on your PC. The formula we are using includes a percentage sign (“%“), which represents a percentage value. Depending on the regional settings or version of Excel being used, the formula may not recognize the percentage sign as a valid argument. To avoid any potential compatibility issues, you can modify the formula to use a decimal value instead of a percentage. For example, you can use “1” to represent 100%. I just told it as an example. It could be better if you tell me which formula isn’t working and which Excel version are you using.
Hope you can understand me. Happy Excelling.

Regards,
SHAHRIAR ABRAR RAFID
Team ExcelDemy

Hello GEORGI,
Thanks for appreciating our work. We are also happy to release you from prolonged suffering. Now, get back to your query.
Sure, you can alter the “Destination” parameter of the “QueryTables.Add” method in your script to specify a specific range to extract from the Google sheet.
For instance, you could change the code such that it retrieves data from cells A1 to E10 of the sheet:

Sub Import_Sheets_to_Excel()

    Dim QRT As QueryTable, ul As String, ky As String
    If ActiveSheet.QueryTables.Count > 0 Then ActiveSheet.QueryTables(1).Delete
    ActiveSheet.Cells.Clear
    ky = "1slumgR5et-cG7Er-9udweCSthufb7xYdsmoP9H2AOFs"
    ul = "https://spreadsheets.google.com/tq?tqx=out:html&key=" & ky & "&range=A1:E10"
    Set QRT = ActiveSheet.QueryTables.Add(Connection:="URL;" & ul, _
    Destination:=Range("$A$4"))
    With QRT
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .Refresh
    End With

End Sub

The “ul” variable in this modified code has the addition “&range=A1:E10” to identify the range to extract from. Also, the starting cell where the extracted data will be stored is specified by the “Destination” parameter in the “QueryTables.Add” method, which is set to “$A$4“.
I hope this helps. I wanted to personally invite you to check out our new Excel-related forum. We’ve created a space for Excel enthusiasts like us to share tips, tricks, and ideas, as well as to ask and answer questions about using Excel. We’re a growing community of Excel users, and we’d love to have you join us!
Here’s the link to our forum: ExcelDemy Forum

Regards,
SHAHRIAR ABRAR RAFID
Team ExcelDemy

Hello REZA,
Thanks for commenting and asking your valuable question. Actually, it’s quite costly to do it with VBA. Because of this, you have to enable the Google Sheets API, and quite expensive. So I wouldn’t suggest it.
Rather, you can use Google Drive or the Import feature of Google Sheets to do this task easily. You can follow this linked article on our website to get the whole idea.
Anyway, if you want to do it with VBA, you can follow this lengthy process:

• First, you have to enable the Google Sheets API for your Google account and create a project in the Google Developers Console. Here are the steps to enable the Google Sheets API and create a project in the Google Developers Console:
• Go to the Google Cloud Console website (https://console.cloud.google.com/).
• If you don’t have a Google Cloud account, sign up for a free trial or create a new account.
• Once you’re signed in to the Google Cloud Console, create a new project by clicking the “Select a project” dropdown menu in the top navigation bar and clicking “New Project“.
• Give your project a name and click “Create“.
• In the left-hand navigation menu, click “APIs & Services” and then click “Dashboard“.
• Click the “+ ENABLE APIS AND SERVICES” button at the top of the page.
• In the search bar, type “Google Sheets API” and click on the result.
• Click the “ENABLE” button to enable the Google Sheets API for your project.
• Next, you need to create a set of credentials to authenticate your VBA program with your Google account. In the left-hand navigation menu, click “Credentials“.
• Click the “Create credentials” dropdown menu and select “OAuth client ID“.
• Select “Desktop app” as the application type, give your client ID a name, and click “Create“.
• In the “OAuth client created” dialog box, click “OK“.
• Click on the name of the client ID you just created to download the client secret file.
• Rename the downloaded file to “client_secret.json” and save it in a secure location on your computer.
• Finally, authorize the API scopes for your Google account by clicking the “Configure consent screen” button on the “Credentials” page and following the prompts to enter your app details and authorized scopes.
• After that, Create a Google Sheets spreadsheet and give it a name.
• In VBA, create a reference to the Google API client library and authenticate to your Google account. Here is a sample VBA code to do it:

' Load the Google API client library
Private Sub LoadGoogleAPI()
    Dim objHTTP As Object
    Set objHTTP = CreateObject("MSXML2.XMLHTTP")
    objHTTP.Open "GET", "https://developers.google.com/oauthplayground/assets/js/library.js", False
    objHTTP.send
    Dim html As Object
    Set html = CreateObject("htmlfile")
    html.body.innerHTML = objHTTP.responseText
    Dim script As Object
    Set script = html.createElement("script")
    script.Language = "javascript"
    script.Text = "function DoNothing() {}"
    html.appendChild script
    Set google = html.parentWindow
End Sub

' Authenticate to Google Sheets
Private Sub AuthenticateToGoogle()
    Dim CLIENT_ID As String
    Dim CLIENT_SECRET As String
    Dim REFRESH_TOKEN As String
    CLIENT_ID = "your_client_id_here"
    CLIENT_SECRET = "your_client_secret_here"
    REFRESH_TOKEN = "your_refresh_token_here"
    
    ' Load the Google API client library
    LoadGoogleAPI
    
    ' Authenticate with Google
    google.gapi.auth.authorize Array("https://www.googleapis.com/auth/spreadsheets"), CLIENT_ID, CLIENT_SECRET, REFRESH_TOKEN
    
    ' Get the access token
    access_token = google.gapi.auth.getToken().access_token
End Sub

' Upload data to Google Sheets
Private Sub UploadDataToGoogleSheets()
    ' Authenticate with Google
    AuthenticateToGoogle
    
    ' Define the range of data to upload
    Dim range As String
    range = "Sheet1!A1:D4" ' Change this to the range of your data
    
    ' Get the spreadsheet ID
    Dim spreadsheet_id As String
    spreadsheet_id = "your_spreadsheet_id_here"
    
    ' Define the data to upload
    Dim data As Variant
    data = Range(range).Value
    
    ' Upload the data to Google Sheets
    Dim url As String
    url = "https://sheets.googleapis.com/v4/spreadsheets/" & spreadsheet_id & "/values/" & range & "?valueInputOption=USER_ENTERED"
    Dim xmlhttp As Object
    Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
    xmlhttp.Open "PUT", url, False
    xmlhttp.setRequestHeader "Authorization", "Bearer " & access_token
    xmlhttp.setRequestHeader "Content-Type", "application/json"
    Dim json As String
    json = "{""values"": " & WorksheetFunction.Transpose(WorksheetFunction.Transpose(JsonConverter.ConvertToJson(data))) & "}"
    xmlhttp.send json
End Sub

Just make sure to change specific things in your own code. Hope this could help you. Again, thanks to you.

Regards
SHAHRIAR ABRAR RAFID
Team ExcelDemy

Hi JORGE,
You asked for an interesting extension of this documentation. It’s all the same. Just have to make some modifications. Follow the code below:

Option Explicit

Public Function Driving_Time(startlocation As String, destination As String, keyvalue As String)

Dim First_Value As String, Second_Value As String, Last_Value As String, mitHTTP As Object, mitUrl As String

First_Value = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
Second_Value = "&destinations="
Last_Value = "&travelMode=driving&o=xml&key=" & keyvalue & "&distanceUnit=mi"

Set mitHTTP = CreateObject("MSXML2.ServerXMLHTTP")

mitUrl = First_Value & startlocation & Second_Value & destination & Last_Value & "&routeAttributes=routeDuration"

mitHTTP.Open "GET", mitUrl, False
mitHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
mitHTTP.Send ("")

Driving_Time = Round(Round(WorksheetFunction.FilterXML(mitHTTP.ResponseText, "//TravelDuration"), 3) / 60, 0)

End Function

Here, we renamed the function to Driving_Time. And changed the mitUrl variable.

mitUrl = First_Value & startlocation & Second_Value & destination & Last_Value & "&routeAttributes=routeDuration"

Also, we modified the last line of the code to:

Driving_Time = Round(Round(WorksheetFunction.FilterXML(mitHTTP.ResponseText, "//TravelDuration"), 3) / 60, 0)

Here, we divided with 60 to get the time in minutes. Otherwise, it’ll return the time in seconds.
Hope, you find it helpful. Btw, are you Spanish? I like the spelling of your name. Thanks again.

Regards,
SHAHRIAR ABRAR RAFID
Team ExcelDemy

Hello again SALIH,
How are you? I know it feels so irritating when your script doesn’t run properly. So, I’ve come up with a bit of modification to the previous one. Hope it’ll not disappoint you.

Sub SelectedDatatoBottom()
    Dim x As Worksheet
    Dim ExistingTable As ListObject
    Dim mTable As ListObject
    Set x = ActiveSheet
    Set mTable = x.ListObjects("Pipe_Live")
    On Error Resume Next
    Set ExistingTable = Worksheets("RollingPivot").ListObjects("LongTable")
    On Error GoTo 0
    If ExistingTable Is Nothing Then
        MsgBox "The target table 'LongTable' was not found in the 'RollingPivot' worksheet.", vbCritical
        Exit Sub
    End If
    If ExistingTable.ListRows.Count = 0 Then
        ExistingTable.ListRows.Add
    End If
    Dim targetRange As Range
    If ExistingTable.ListRows.Count = 0 Then
        Set targetRange = ExistingTable.DataBodyRange.Rows(1)
    Else
        Set targetRange = ExistingTable.ListRows(ExistingTable.ListRows.Count).Range.Offset(1)
    End If
    mTable.DataBodyRange.Resize(mTable.DataBodyRange.Rows.Count).Copy
    targetRange.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Worksheets("RollingPivot_test").Activate
End Sub

Thanks for your query. Feel free to ask if you need any assistance regarding Excel or Office-related applications. Happy Excelling…

Regards,
Shahriar Abrar Rafid
Team ExcelDemy

Hello JOHAN,
First, thanks for your query. Actually, dynamic named ranges created with OFFSET, and COUNTA functions don’t work when copied to another workbook. You can use a workaround instead. Follow the steps below.
• At first, open the Name Manager.
• Then, click on any name and tap on the Edit button.

Instantly, it will open the Edit Name dialog box.
• Here, change the previous formula in the Refers to box and give this new one.
=INDEX(Sheet1!$B$2:$C$23,0,MATCH(Sheet1!$C$2,Sheet1!$B$2:$C$2,0))
• As usual, click OK.

• Similarly, do the same for the second name also. The formula for this is similar also.
=INDEX(Sheet1!$B$2:$C$22,0,MATCH(Sheet1!$B$2,Sheet1!$B$2:$C$2,0))

Now, watch the GIF. It’s working in the new workbook.

And the chart is still dynamic. It’s changing while you are inputting new values.
That’s all from me on this topic. Hope you find this helpful. Follow our website ExcelDemy to explore more about Excel. Happy Excelling.
Regards
SHAHRIAR ABRAR RAFID
Excel & VBA Content Developer
Team ExcelDemy

Hello HANNA,
First of all, thanks for your valuable comment. I think I have got your problem. Follow the steps below.
Here’s a sample and simple dataset that I created from your query.

Now, using this dataset, we’ll pick the top 3 Salesman names and their corresponding sales amount.
• At first, go to cell B16 and enter the following formula.
=INDEX($B$5:$B$13,MATCH(LARGE($C$5:$C$13,1),$C$5:$C$13,0))
• Then, press ENTER.

• After that, bring the cursor to the right-bottom corner of cell B16 and you will find the Fill Handle tool visible.
• Now, drag the tool up to cell B18.

In the following cells of Column B, change the k argument of the LARGE function according to the position. See the image below.

• To obtain their sales amount, go to cell C16 and insert the formula below.
=LARGE($C$5:$C$13,1)
• As usual, tap ENTER.

You can add an extra column just beside the dataset to show the rank of the salesmen according to their sales amount. The formula used in cell D5 is the following.
=RANK(C5,$C$5:$C$13)

That’s all from me on this topic. Hope you find this helpful. Follow our website ExcelDemy to explore more about Excel. Happy Excelling.
Regards
SHAHRIAR ABRAR RAFID
Excel & VBA Content Developer
Team ExcelDemy

Hello LINDSEY SERVE,
Sorry to hear about the problem you are facing. Have you tried it with the Practice Workbook? I tried it again and it still works. Are you trying it with your own workbook? Then, I think there could be some issues with this file. I could help you if you share it with us in the mail ID [email protected]. Thanks again.
Regards,
Shahriar Abrar Rafid
Excel & VBA Content Developer
Team ExcelDemy

Hello JOEY,
Actually, those columns have been deleted. If there were any data you would understand then. But, you got confused because all the deleted columns get replaced instantly with new ones and it happens so fast that we cannot detect it with our eyes. If you fill the first row with all columns and then apply this method, you could realize the change.
Thanks for your feedback. Please visit our website, ExcelDemy, a one-stop Excel solution provider, to explore more.

Hello THORSTEN LEMANN,
I’ve got your problem. I tried to replicate your dataset and also, applied the VLOOKUP function to fetch the monthly sales of a particular sales rep based on his/her ID. See the image below.

Notice that my lookup_value is in the first column of my table_array. Always make sure to maintain this. Otherwise, the VLOOKUP function will not work. Your mistake was that you were keeping your lookup_value in the second column of your table_array. That’s why the formula wasn’t operating and showing #N/A as output.
So, be cautious next time. That’s all from me on this topic. You may follow our website, ExcelDemy, a one-stop Excel solution provider, to explore more. Happy Excelling.

Hello REBECCA,
Thanks for your feedback. We are very glad to feel that our readers study our articles attentively. I am very sorry that method was not added. Now, I’m showing the way step by step for your convenience.
• At first, select the cells in the B4:F14 range.
• Then, press the CTRL key followed by the T on your keyboard.

Immediately, the Create Table dialog box appears.
• Secondly, click OK.

As a result, the normal data range is converted into a table.

• Thirdly, select cells in the Student column.
• Then, go to the Data tab.
• Now, click on Sort A to Z.

You can see that the whole dataset became sorted along with this column.

That’s all from me on this topic. Again, many thanks to you for your subtle observations. I’ll inform it to our editorial team to fix it. We always hope for such positive comments. Follow our website, ExcelDemy, a one stop Excel solution provider, to explore more.
Regards,
Shahriar Abrar
Excel & VBA Content Developer
Exceldemy

It’s my pleasure. I’m happy to be of service.

Hello V SHANTHI,
First of all, thanks for your appreciation. Now, let’s get back to your query. Here, you wanted to add a new column for showing the leave balance of employees. If you want to do this on a monthly basis, follow the steps below.
• At first, create a new column with the heading Leave Balance under Column AO.

• Then, go to cell AO6 and enter the following formula.
=4-AN6
Here, we assumed 4 leaves can be taken in a month. It could be changed according to your need.
• After that, press the ENTER button.

• Now, bring your cursor to the right-bottom corner of cell AO6 and it’ll look like a plus (+) sign. It’s the Fill Handle tool.
• Afterward, double-click on it.

You can see the results in the following cells also.

On the other hand, if you find out the employee leave balance or remaining leaves on a yearly basis, you can follow the article How to Track Employee Vacation Time in Excel on our website.
So, that’s all from me on this topic. Please visit our website, ExcelDemy, to explore more. Happy Excelling.

Hello ANTHONY,
Here, we’ll get the value of the last cell of a row and show it in cell C14.

But, as per your condition, we’ll retrieve this value from another workbook named Sales. Now, we want to extract the value of the last cell of Row 5 from the Dataset worksheet of this workbook.

To do this,
• At first, select cell C14 and call the INDEX function.

• Then, select cells in the B5:E5 range.

• After that, insert the COUNTA function and select the same range as the argument.
• As usual, press the ENTER button.

• Finally, you can see the result in cell C14 and the final form of the formula is like the following.
=INDEX([Sales.xlsx]Dataset!$A$5:$D$5,COUNTA([Sales.xlsx]Dataset!$A$5:$D$5))

Here, the part inside the square brackets are the name of the workbook. And the part before the exclamation mark and after the square bracket is the name of the worksheet.
That’s how you can do it from another workbook. It’s all from me on this topic. Follow our website, ExcelDemy, a one-stop Excel solution provider, to explore more.

Hello,
First of all, download the Practice Workbook for better understanding.
I think you’ve talked about the following phenomena. Let’s see the following image.

Now, you want to match the items in Column 2020 with the items in Column 2019.
For this, copy the cells in the B5:B12 range.
Then, paste them in the E5:E12 range.

After that, go to cell F5 and enter the following formula.
=IF(COUNTIF(C5:C12,B5:B12)>0,B5:B12,"")
As usual, press ENTER.

Note: It’s an array formula. If you are using Microsoft Excel 365 then you can easily run the formula by pressing the ENTER key. Otherwise, you have to tap the CTRL+SHIFT+ENTER keys simultaneously to make the formula work.
Hope that will work for you. Follow our blog Exceldemy to learn more about Excel.

Hello DEANNA,

First of all, many many thanks for your appreciation. At the end of the day, these kinds of words motivate us a lot. Now, get back to your query.

For your convenience, download this practice workbook.

Firstly, we’ve created a dataset as per your description. We’ve constructed an imaginary Weekly Sales Report of your company. Let’s see the following picture.

Now, we’ll find out how many sales reps cannot cross the Sales Amount of $350 in a week.

At first, go to cell D11 and enter the following formula into the cell.
=COUNTIF(D5:D9,"<350")
Secondly, press the ENTER key.

Here, we can see that Excel returns 2 as result. Because two employees made sales amount below $350 which are Person 2 and Person 5. We think you wanted to know about this part.

As a bonus, we’ll teach you one more trick. You can get a more specific answer with this. Guess, if you wanted to know how many sales reps made sales amount below $350 in a specific zone. Let’s say, in the West zone. Obviously, you can crack this too.

Firstly, select cell D11 and write down the formula below.
=COUNTIFS(D5:D9,"<350",C5:C9,"West")
As usual, tap ENTER.

The result is 1 because there is only one Sales Rep in the West zone with a Sales Amount less than $350 who is Person 2.
To explore more about Excel, please visit our website Exceldemy: One-stop Excel solution provider…

Hello MOHAMAD,
Thanks for your valuable comment. We always expect such positive feedback from our users.
As you said, here we’ve shown the simple exponential method only. The main reason for this is that we didn’t only want to emphasize exponential smoothing; rather we wanted to cover all conceivable methods of data smoothing. If so happened the article would then be extremely lengthy.
But you can find triple exponential smoothing widely called the Holt-Winters Exponential Smoothing on our website also. This method is very much adaptable for data with seasonal patterns.
Thanks Again.

If your workbook is shared, anyone who has Write privileges can clear the read-only status.

In this situation, you can use an easy alternative. Try using:
=COUNTA(UNIQUE(date range))
The above formula can easily count the unique date values.