Akib Bin Rashid

Hi LILIA,
I hope you are doing well.
Please use the conditions as per the image and see if it works.
\
The range $E$4:$E$100 will allow you to have a list of 97 items. So, this can be used as a dynamic drop-down
Thank you.

Hi DAVID,
I hope you are doing well.
In Excel, you cannot do so as far as I know. However, you can make the fill color same as the background color to technically avoid printing the row.
And you can also hide specific rows and columns to not print them.
Thank you. Have a good day.

Hello STEEN and DAV,
I have checked the methods on several laptops. They are working well here.
For your convenience, I have added a new Excel file with the VBA code.
Please download it and check if the methods are working.
Thank you. Have a good day.

Hi SANKARSHAN
Generally, you can set one CRITERIA when you apply only COUNTIF Function.
However, if you use a formula that has multiple functions, you can set multiple criteria.
Let me show you an example.
● Suppose you have a set of fruits. You want to count the instances when the fruit is either Mango or Apple.

● Go to D5 and write down the following formula
=COUNTIF(B4:B15,B4)+COUNTIF(B4:B15,B5)

Here, Excel will count the instances when the criteria is Mango or Apple from the same range B4:B15.
● Now, press ENTER to get the output.

I hope it helps.
You can also check the following articles to internalize the concept.
● COUNTIF with Multiple Criteria in Different Columns in Excel
● COUNTIFS with Multiple Criteria (5 Easy Methods)

Thank you.

Hi Marko
I hope you are doing well.
After going through your problem, I will give you 2 possible solutions.
Solution 1:
This will require you to insert the number after “WA11” within parentheses.
● Add the parentheses.

● Then, write down the following formula in B2.
=MID(A1,SEARCH("(",A1)+1, SEARCH(")",A1)-SEARCH("(",A1)-1)+0

● Then, press ENTER. Excel will extract the number inside the parentheses.

● Then, AutoFill up to B3.

● Finally, add the numbers.

Solution 2:
● This will require you to have a helping column with the numbers with “WA11”. But this is a bit tedious if you have a lot of data.

● Now, add the numbers.

Hope this helps. Thank you.

Hi G0DWIN,
Thank you for your kind words!

Hi NGÂN,
The solution you want will require a combination of some functions like TODAY, COUNTIF, VLOOKUP, etc. Here is a post on our website that will help you.
https://www.exceldemy.com/stock-ageing-analysis-formula-in-excel/
We have several posts related to this topic too.
https://www.exceldemy.com/make-inventory-aging-report-in-excel/
https://www.exceldemy.com/excel-ageing-formula-for-30-60-90-days/
https://www.exceldemy.com/aging-of-accounts-receivable-in-excel/
I hope these articles will help get your job done. If not, please remember that we are just a text away!!
Thank you. Have a good day.

Dear Tiago
It’s good to know that you have finished your work successfully. No, I have not tried the MLE method for Weibull Distribution yet.
Thank you. Have a good day!

Hello MAX
Thank you for your comment. I have examined the entire workbook properly. As per my understanding, the sheet works fine.
The “Monthly” option in Extra Payment Frequency will be available if you select the “Monthly” option in the Regular Payment Frequency field. Actually, the options in Extra Payment Frequency are dependent on the Regular Payment Frequency field.

Coming to the 2nd issue, the summary table updates properly on my device. In your case, could you please check the version of Excel you are operating? This sheet has some advanced functions that may not work in all Excel versions.

Please let us know if you are satisfied with this answer. If not, please feel free to ask us. You can even send your dataset to the following address: [email protected]

Thanks again. Have a good day!

Hi Mohammed
Thank you for your appreciation. If you want to incorporate ERC in your final balance, I think your method is a nice one.
I would, however, suggest one thing. Keep the ERC in column I and calculate the final balance in column J.

Hi Jack
Thank you for your query.

It would be very kind of you if explain your problem in detail. Perhaps, you can send us your problem at [email protected] to get better assistance.

From your query, it seems like you want to calculate in Excel using the RATE function and insert a certain fee as a percentage in the function.

The RATE function returns the interest rate per period of an annuity. The syntax of the RATE function is as follows.

RATE(nper, pmt, pv, [fv], [type], [guess])

Where,
● Nper: Required. The total number of payment periods in an annuity.
● Pmt: Required. The payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.
● Pv: Required. The present value — the total amount that a series of future payments is worth now.
● Fv: Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). If fv is omitted, you must include the pmt argument.
● Type: Optional. The number 0 or 1 and indicates when payments are due.
● Guess: Optional.

As you can see, you cannot directly incorporate any fee as a percentage in the RATE function. However, you can indirectly use a percentage in a formula that has the RATE function.

I am going to discuss 2 such examples.
Example 1:

Let’s assume that you have borrowed $8000 for 4 years. The monthly payment is $200. Now, if you use the RATE function here, you will get a 9.24% annual loan rate. But in practice, you may need to adjust your loan. So in that case, you can incorporate a percentage.

The formula in C8 is
=RATE(C3*12, (-1)*C4, C5)*12-C7

In the image above, we have subtracted a percentage (0.07%, our adjusting factor in C7) from the annual rate of the loan. In a similar fashion, you can manipulate your dataset the way you want.

Example 2:
Suppose you got a loan of 20% on the value of your certain asset. Now, you can incorporate this 20% in the RATE function. The formula in C7 is

=RATE(C3*12, (-1)*C4, C5*20%)*12

Here, C5*20% represents the loan amount ($8000).
This is how you can incorporate any value indirectly in the RATE function. To learn more, have a look at this article.

How to Use RATE Function in Excel (3 Examples)

Hi JOAO
Thank you for your query.
Yes, you can count cells with specific text and font color in a range covering Multiple Columns using VBA.
I will show you two cases regarding how to count cells by font color of specific text.

Case 1: Count Cells by Font Color of Specific Text
First, let me explain the case a bit. Please have a look at the image.

Here, the font color in the specific text “The Prince of the Skies” (in E5) is Red, which is present in both B and C columns. To count the cells that have the same font color of that specific text, you should run the code given below:

Sub CountCellsByFontColor()
    Dim rng As Range
    Dim lookupCell As Range
    Dim count As Long
    
    Set rng = Range("B5:C12")
    Set lookupCell = Range("E5")
    
    For Each cell In rng
        If cell.Font.Color = lookupCell.Font.Color Then
            count = count + 1
        End If
    Next cell
    
    Range("F5") = count
End Sub

Note:
cell.Font.Color = lookupCell.Font.Color → With this command, Excel is matching the font color of each cell (in Range B5:C12) with the font color of cell E5. If they match, the count variable will increase by 1.

Once you run the code, Excel will show you the result.

Case 2: Count Cells with Specific Text and Font Color
Let’s understand this case first.

Here, the reference text is “Pippo and Clara”. However, the font color is different in B6 and B8.
Now, to count cells by specific text with color, write down the following code:

Sub CountCellsByTextAndColor()
    Dim count As Long
    Dim tableRange As Range
    Dim lookupText As String
    Dim lookupColor As Range
    Dim cell As Range
    lookupText = InputBox("Specific Text: ")
    
    Set tableRange = Range("B5:C12")
    
    Set lookupColor = Range("E5")
    
    count = 0
    
    For Each cell In tableRange
        If cell.Value = lookupText And cell.Font.Color = lookupColor.Font.Color Then
            count = count + 1
        End If
    Next cell
    Range("E5") = lookupText
    Range("F5") = count
    
End Sub

Once you run the code, you will get an input box.

You have to write the specific text that you desire. Then, on clicking OK, Excel will show the result.

Note:
cell.Value = lookupText And cell.Font.Color = lookupColor.Font.Color → This command is checking two conditions
cell.Value = lookupText: This condition checks if the value of the cell is equal to the value of the lookupText variable.
cell.Font.Color = lookupColor.Font.Color: This condition checks if the font color of the cell is the same as the font color of the lookupColor cell.
If both condition match, the count variable will increase by 1.

Here, the count is 1. Because, for the text (“Pippo and Clara”), the font color matches only once in cell B8.
Thank you. Have a good day!

Hi PRATIBHU
I hope you are doing well.

This is your 2nd question and its answer.

If the excel is saved in OneDrive, and opened via Web browser, will the macros still run?

Answer: It depends on the type of macro and how it was created.
If the macro is a VBA macro, it will not run in the web browser version of Excel. VBA macros can only run in the desktop version of Excel on a Windows or Mac computer.

Coming to your first question, our team is working on the macro. We will reach you soon hopefully.

Hi BALA Sir
Thank you for your comment.
To get the current mutual fund NAVs, you can use the following link:
https://scripbox.com/mutual-fund/latest-nav
You can get the latest NAVs using PowerQuery (method 3 in this article).
You will need to activate the “link to data” option to get the automatic update.
I think it should work this way. Please let us inform if you face any issues.
Thank you

Hi JUNED SHAIKH
I hope you are doing well. The term “Multi Validation” is a bit confusing. However, I would like to provide you with some information regarding Data Validation in Excel.
1. You cannot have multiple columns or rows as your data source for data validation. The data source must be a single row or column.
2. You can apply data validation to one specific cell once at a time. If you want to reapply data validation to one particular cell, you have to remove the previous data validation.
3. You can apply data validation to the entire sheet keeping the procedure congruent with the above statements.
I hope it helps. Have a great day.