User Posts: Maruf Islam
Editor choice
0
How to Find Lowest 3 Values in Excel (5 Easy Methods)
0

Often, we come across a situation where we need to find the lowest values in a dataset. In this article, we’ll use SMALL, ROW, and AGGREGATE functions as well ...

Editor choice
0
How to Set a Minimum and Maximum Value in Excel (6 Easy Methods)
0

In a dataset sometimes we need to input entries between ranges. In that case, entries between one minimum and one maximum value represent a whole dataset. In ...

Editor choice
0
How to Remove Non-Printable Characters in Excel (4 Easy Ways)
0

Every Character printable or non-printable bears a Unicode Character Code or Value. The First 32 characters (0-31) of Unicode or ASCII in programs to control ...

0
How to Count Odd and Even Numbers in Excel (3 Easy Ways)
0

Sometimes in a dataset, we have to count odd and even numbers in Excel to understand the entries. In this article, we’ll describe ways to count odd and even ...

0
COUNTIFS with Multiple Criteria (5 Easy Methods)
0

COUNTIF counts cells in a range depending on a single criterion. Whereas COUNTIFS count cells in one or multiple ranges depending on multiple criteria. This ...

0
How to Compare Three Columns in Excel Using VLOOKUP
0

VLOOKUP is a lookup and reference function to find and fetch data from a range in Excel. VLOOKUP finds and compares values by rows for a single criterion. To ...

Editor choice
0
How to Number Rows Automatically in Excel (8 Methods)
0

Excel does not offer any features to number data while we enter/import them. It’s become difficult to understand any dataset without numbering. For better ...

Editor choice
0
Remove Dashes from Phone Number in Excel(4 Ways)
0

In datasets, we come across phone numbers containing Dashes (-). For obvious reasons, we have to remove the dashes from the phone number entries. In this ...

Editor choice
0
How to Delete Defined Names in Excel (3 Ways)
0

Defined Names assigned to a single cell, range of cells, formulas, tables, and so on; are used in order to increase convenience when those elements need to be ...

Editor choice
0
Excel Show Formula as Text in Another Cell (4 Easy Methods)
0

In Excel, you enter a formula, select references and press Enter. And drag the Fill Handle to apply the formula to the rest of the cells. Now, you want just ...

Editor choice
0
How to Delete Multiple Columns in Excel
0

Deleting multiple columns; empty or unnecessary is quite a desired task in Excel operations. In this article, we use Excel tools such as Delete, Cell Option, ...

0
How to Delete Blank Cells in Excel and Shift Data Up
0

Blank cells in Excel dataset are not unusual. When they are in the right place in the dataset, blank cells provide eye-soothing views; but blank cells in ...

0
How to Merge and Center Cells in Excel (3 Easy Methods)
0

When an entry occupies more than one cell and needs to be positioned in the center, Excel offers a couple of methods to go with. In this article, we’ll show ...

0
How to Copy Formatting in Excel to Another Sheet (4 Ways)
0

Font, Number, Date types;  Alignment; Bold, Italic Headings; Coloring; Designing; Size of cells; etc. are different kinds of formats available in Excel. From ...

Editor choice
0
How to Merge Two Rows in Excel (4 Easy Ways)
0

In this article, I am going to discuss the available methods offered by Excel to merge two consecutive rows into one single row. I use Merge & center, ...

Browsing All Comments By: Maruf Islam
  1. Welcome @Janice

  2. Greetings Jessica,

    Use the SUMIFS function to impose multiple criteria like Product Category, Product, and Dates to find out the total sales of products (i.e., Chocolate Chip or Carrot).

    For Chocolate Chips =SUMIFS(G4:G17,E4:E17,J4,F4:F17,J5,B4:B17,">="&J6,B4:B17,"<="&J7)

    For Carrot =SUMIFS(G4:G17,E4:E17,M4,F4:F17,M5,B4:B17,">="&M6,B4:B17,"<="&M7)

    Find Sales

    You can also add all the different sales afterward. Hope, this may help you.

    Regards
    Maruf Islam (Exceldemy Team)

  3. Greetings Ahsan Ahmed,

    It’s not clear why you want to use the INDEX-MATCH formula in the first place. However, if I assume you want to use the INDEX-MATCH formula for fetching some matching values, then you want to Add, Subtract, Multiply, or Divide them. You can use the below formula to return the matched values. And afterwards, you can execute any Arithmetic Operations you want.

    =IFERROR(INDEX($D4:$D$9,IF(ISNUMBER(MATCH($B$4:$B$9,$F$6,0)),MATCH(ROW($B$4:$B$9),ROW($B$4:$B$9)),""),ROWS($A$1:A1)),"")

    Execution

    Hope, the formula may satisfy your cravings.

    Regards
    Maruf Islam (Exceldemy Team)

  4. Greetings Dr. Linda Vandergriff,

    Go through the following methods to get rid of the inconvenience. Make sure the value cells are in the Currency or Accounting Number Format.

    1. Manually delete the preceding Apostrophes. Or

    2. Use a formula to auto remove the Apostrophes as shown in the image.

    =NUMBERVALUE(SUBSTITUTE(B2,"'",""))

    Formula

    Feel free to comment if you have further inquiries. We are here to help.

    Regards
    Maruf Islam (Exceldemy Team)

  5. Greetings Jack Kuehne,

    Thank you for your appreciation.
    To make static only one (01) calculated cell value or range values, follow the below steps:

    1. Unlock all cells within the worksheet using the Format Cells window (CTRL+1 or Home > Click Font Setting Icon to open Format Cells window > Protection > Untick Locked) [Shown in the latter image].

    2. Highlight the desired cell or range (you want to keep static no matter what) then Lock them using Step 1 (Tick Locked in the Protection section of Format Cells window).

    3. Go to the Preview tab > Tick Select Locked Cells and Select Unlocked Cells > Enter Password > OK > Re Enter Password.

    Step 2 and 3 represent Method 1 of this article.

    Untick Locked

    Regards
    Maruf Islam (Exceldemy Team)

  6. Greetings Ganesh,

    You can find the delivery date using SUM or a simple Arithmetic Formula, as I used in the following picture.

    =SUM(B3,7*C3 or D3)
    or
    =B8+7*C8 or D8

    Formula

    I hope this solves your seeking. Comment, if you need further assistance.

    Regards
    Maruf Islam (Exceldemy Team)

  7. Greetings Ashif,

    You can manipulate (Hide or Unhide) the Rows of a Protected Worksheet by ticking the Use AutoFilter option under Allow all users of this worksheet to:

    [Go to the Review tab > Protect / Unprotect Sheet > Tick Use AutoFilter > Enter Password > Click OK]

    Hiding Rows in Protected Worksheet

    I hope this may work in your case. Let me know your thoughts in the comment section.

    Regards
    Maruf Islam (Exceldemy Team)

  8. Greetings Julian Chen,

    Sadly, the HYPERLINK function doesn’t support any attachment links in its arguments. You have to use Other Means to attach files. You can use Method 3 of this article to include an attachment.

    Regards,
    Md. Maruf Islam (Exceldemy Team)

  9. Greetings Ramin Janani,

    strCharacter = "("
    endCharacter = ")"

    This part of the macro takes the 1st brackets as characters to trigger the macro to Color Text or Characters within them. Make sure you encompass the text or characters with brackets. I’ve tested the macro in languages other than English and it works.
    Of course, you can change the strCharacter and endCharacter with whatever character (i.e., (),{},[]) you assign to in the macro.

    Regards,
    Md. Maruf Islam (Exceldemy Team)

  10. Greetings Tom,

    You are right. You can calculate the Total Paid or Payable Amount of a loan using PMT*nper or Cumulative Interest + Loan Amount. And the amount will be the same.
    Make sure you enter the Start_period (i.e., minimum 1) and End_period (i.e., within the Total Periods (60)) correctly.

    Total Amount of a Loan

    Regards,
    Md. Maruf Islam (Exceldemy Team)

  11. You are welcomed Jaisinh Chavan. Thanks for your kind words.

    Regards
    Maruf Islam (Exceldemy Team)

  12. Greetings Mani,

    Use the following macro code to copy data (from tables or normal ranges) to the bottom of an existing table (i.e., Table1 or anything).
    The macro fetches an input box to select the preferred range to copy and paste afterward. Change the Worksheet Name (i.e., vba) and Table Name (i.e., Table1) as existing table.

    Sub SelectedDatatoBottom()
    Dim ExistingTable As Object
    Dim mTable As Range
    On Error Resume Next
      Set mTable = Application.InputBox( _
          Title:="Copy Data to Paste to Bottom", _
          Prompt:="Select Cells to Copy", _
          Type:=8)
      On Error GoTo 0
    Set ExistingTable = Worksheets("vba").ListObjects("Table1").ListRows.Add
    mTable.Copy
    ExistingTable.Range.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Worksheets("vba").Activate
    End Sub

    Download the Excel file for your better assistance.

    Regards
    Maruf Islam (Exceldemy Team)

  13. Dear Tijs Tijert,

    The used formula shouldn’t react this way. Also, I’m not sure what your data and values are that you used as criteria. Kindly email me your dataset to [email protected]. I’ll try my best to come up with a solution to your problem.

    Regards
    Maruf Islam

  14. Greetings Joseph Clovis,

    The AND and FILTER functions do take cell references to filter and fetch entries that fall under the condition (Date>= T0DAY()-30 <TODAY()) respectively. However, sadly, the Excel Filter feature doesn’t support selecting dates from ranges or entries. All the Filter execution does is that it hides rows, which may lead to inconveniences sometimes. Till now, there has been no alternative to selecting dates from the Filter dialog box options. But you can try Conditional Formatting or VLOOKUP function for highlighting and fetching data. And in those cases, you can use cell references from your dataset.

    1. Conditional Formatting: Highlight Cells or Range > Go to Home > Conditional Formatting > New Rule > Select a Rule Type and Format > OK.

    =AND($B5>=(TODAY()-30),$B5<TODAY())

    Conditional Formatting

    2. VLOOKUP Function: Use TODAY()-30 in any cell, then use the VLOOKUP formula to fetch different entries. In that way, the dataset range remains intact.

    =TODAY()-30
    =VLOOKUP(F5,$B$5:$D$16,2,0)
    =VLOOKUP(F5,$B$5:$D$16,3,0)

    VLOOKUP Formula

    Hope, these ways help you to compensate the Excel Filter caveats. Feel free to comment if the solution doesn’t satisfy your seeking. Our Exceldemy Team is always there to help.

  15. Greetings Indra,

    Thanks for your appreciation.

    The probable causes behind the freezing of Visual window or Excel are:

    1. VBA Macros use device’s single core to run or execute macros.
    2. Large data normally take several minutes to complete any macro-driven outcomes. Therefore, sudden freezing of visual window or Excel is common among users.

    You can use the already used macro (in the article), if it works fine after couple of minutes of freezing or unresponsiveness. Otherwise, email us your Data to get custom macro that may solve the issue (As we don’t have such huge data to test our code with). Also, you can go through:

    1. Divide your data into worksheets then execute the macro individually. Then merge the worksheets into one.
    2. Use other means such as formulas to accomplish the desired outcome.

    Hope, it helps you. Comment if further issues arise.

  16. Hi Taylor,

    # The macro contains conditions against the email sending within a Week prior to the Deadline/Due Date using the following line

    If CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 0 Then
    xValSendRng = XRcptsEmail.Offset(k - 1).Value

    # So, the email will get sent within- 1 day <= Deadline/Due Date <= 7 day or 1 week range prior to the Deadline/Due Date.

    Hope, you find your answer, let us know if further explanation is needed. Our dedicated Softeko Team is always there to help.

  17. Greetings Sepehr,

    Thank you for your appreciation.

    # The answer to your question is “Yes“—it is possible to have the list of products in different columns.

    # Just use the TRANSPOSE function preceding the UNIQUE+XLOOKUP formula. Of course, you have to have Excel 365 to use the XLOOKUP function.

    =TRANSPOSE(UNIQUE(XLOOKUP(B5,B7:D7,B8:D14),,TRUE))

    Product List in Different Column

  18. Hlw Melissa,

    I’m assuming you need to Data Validate multiple selection G7 to H15 (adjacent columns) or whatever the cell is.

    # Modify the 1st IF Condition as ” If Not Intersect(Target, Range(“G7:H15″)) Is Nothing Then ” or Change the ” Range(“G7:H15″) ” portion with Capital Letters. Of course don’t forget to apply Data Validation to those cells first.

    Hope this works. Otherwise, comment with detailed cell references.

    Regards
    Maruf

  19. Hlw Kim, Sorry for being late.

    # Excel Pivot Table normally considers multiple items within a cell as “1”. Therefore, multiple selection within a cell doesn’t have any effect on count. However, conditional counting may have different issue.

  20. Hlw Reena Pujari. Sorry for late reply.

    To apply macro to all cells in the same column, follow:

    1. Apply “Data Validation” to all the cell of a column (Highlight Entire Column > go to “Data” > apply “Data Validation”)

    2. Replace ” If Target.Address = “$D$4″ Then ” line within the macro (with repetition or without repetition) with ” If Not Intersect(Target, Range(“D:D”)) Is Nothing Then “.

  21. Hlw Avery, Sorry for late reply.

    To duplicate this for more than 1 multiple select list option:

    # Replace ” If Target.Address = “$D$4″ Then ” line within the macro (with repetition or without repetition) with ” If Not Intersect(Target, Range(“D4:D5”)) Is Nothing Then “.

    Note: You can have multiple Source ranges assigned for “Target.Address” s using Data Validation.

  22. Thanks for your appreciation @Ed

  23. You can exclude or ignore the blanks using 2 simple tricks.
    1. Use an additional formula in conditional formatting. Go to conditional formatting > New Rule option > select Format Only Cell that contains rule type > Select Blanks from edit rule description > Keep Cell format as no cell format. Click OK.
    2. Go to Conditional formatting > New Rule option> Select Use a formula to determine which cell to format rule type > type “=ISBLANK(Cell Reference)=TRUE” in the Edit the Rule description box > Keep Cell format as no cell format. Click OK.

    Hope these tricks work for you.

ExcelDemy
Logo