exceldashboardschool.com/sum-comma-separated-values/ SUMCSV is a user-defined function and -as you see- works fine.

Hi, please check our website. exceldashboardschool.com/how-to-install-an-excel-add-in/

Thanks for your feedback!

exceldashboardschool.com/dataxl-add-in/

Hi, only Microsoft 365 subscribers can use these functions.

Hi, only Microsoft 365 subscribers can use these functions.

Hi, we are working on the Mac version.

Please check the examples: exceldashboardschool.com/sum-every-nth-row/

Hi! If you want to use the SUMTEXT function, you can use our free add-in: exceldashboardschool.com/custom-functions-add-in/ Visual Analytics Ltd. is committed to providing secure and trustworthy software solutions. To this end, our company holds a valid Code Signing Certificate.

Hi, COLORMATCH is a custom function. You can check how to use it. exceldashboardschool.com/custom-functions-add-in/ Visual Analytics Ltd. is committed to providing secure and trustworthy software solutions. To this end, our company holds a valid Code Signing Certificate.

Hello, it's done! exceldashboardschool.com/free-excel-add-ins-tools/

Excel has no native support to reach the object model. This is the reason why we published a custom function library. exceldashboardschool.com/custom-functions-add-in/

exceldashboardschool.com/profit-and-loss-dashboard-template/

Hi, here is the VBA code of GETNUMBERS function. You can extract all numbers from a given cell. After that, simply sum the cells. Function GETNUMBERS(str As String, Optional n As Variant) As String Dim numArray() As String Static rgx As Object, matches As Object Dim result As String Dim i As Integer ' Ensure the RegExp object is created only once for efficiency If rgx Is Nothing Then Set rgx = CreateObject("VBScript.RegExp") End If ' Setup the regular expression to find numbers, including decimals With rgx .Global = True .pattern = "\d+(\.\d+)?" ' Matches sequences of digits and decimals Set matches = .Execute(str) End With ' If there are no matches, exit the function If matches.count = 0 Then GETNUMBERS = "" Exit Function End If ' Populate the array with the matches ReDim numArray(matches.count - 1) For i = 0 To matches.count - 1 numArray(i) = matches(i).Value Next i ' Determine the output based on the 'n' parameter If IsMissing(n) Then ' Join all numbers with a comma and space as delimiter result = Join(numArray, ", ") ElseIf n = "F" Then ' First number result = numArray(LBound(numArray)) ElseIf n = "L" Then ' Last number result = numArray(UBound(numArray)) ElseIf IsNumeric(n) Then ' Nth number, if the index is valid If n > 0 And n <= matches.count Then result = numArray(n - 1) Else result = "Invalid Index" ' or you can set result to "" to indicate no match End If Else result = "Invalid Argument" ' or you can set result to "" to indicate error End If ' Return the result GETNUMBERS = result End Function

This formula counts the number of unique items with the size "Small." However, as you mentioned, if there are no unique items found, the result might be misleading. To handle this scenario, we can add an IF statement to display a more accurate result when there are no matches. Try using this adjusted formula: =IF(COUNTA(UNIQUE(FILTER(B3:B36, C3:C36="Small"))) = 0, "No unique items found", COUNTA(UNIQUE(FILTER(B3:B36, C3:C36="Small")))) This formula will return "No unique items found" if there are no matching unique items for the criteria. Otherwise, it will return the correct count. Let me know if this resolves the issue!

Hello, Equivalent Formula: =IF(AND(B3>=$E$3, B3<=$F$3), "Yes", "No") Explanation: AND(B3>=$E$3, B3<=$F$3): This checks if the date in B3 is greater than or equal to the start date (E3) and less than or equal to the end date (F3). If both conditions are true (meaning the date is within the range), the IF function returns "Yes". If either condition is false, the IF function returns "No". This formula will give you the same result as the DATEBETWEEN UDF but using only standard Excel functions. DATEBETWEEN is a special function and is part of our free function library: exceldashboardschool.com/custom-functions-add-in/

Yes, you can use the SORT function in Excel to sort based on multiple criteria. Here's how you can structure it based on your example: If you have a table with columns like Sales Name, Profit, and Lost, and you want to sort by: Profit in descending order (highest to lowest) & Lost in ascending order (lowest to highest) You would set up the SORT function as follows: =SORT(TableRange, {ProfitColumnIndex, LostColumnIndex}, {-1, 1}) Here's a breakdown of the formula components: - TableRange: This is the range of your entire table. - ProfitColumnIndex: Replace this with the column number for the Profit column within TableRange. - LostColumnIndex: Replace this with the column number for the Lost column within TableRange. For example, if Profit is in column 2 and Lost is in column 3, and your table range is A2, the formula would be: =SORT(A2:C100, {2, 3}, {-1, 1})

@excelkid Thak you for your guidance .... It's work but couldn't be automatically when the data change.

Great question! Blank cells in the range can indeed affect the result depending on the formula you're using, so let me explain how to handle them. By default, the COUNTIF function treats blank cells as a valid entry and will count them if they are part of the range. For example, if your range contains blanks, COUNTIF(data, data) will include those blank cells, which might lead to unexpected results. To ignore blanks, you can modify your formula slightly by adding a condition to exclude them. For example: =COUNTIF(data, cell_reference) becomes =IF(cell_reference<>"", COUNTIF(data, cell_reference), 0) This checks if the cell is not blank (<>"") before counting it. If you're using the formula =1/COUNTIF(data, data) to calculate proportions, you’ll also need to exclude blanks. Wrap it with an IF condition like this: =IF(cell_reference<>"", 1/COUNTIF(data, data), 0) This ensures that blank cells contribute "0" rather than being incorrectly included in the calculation. If you're using the UNIQUE function to list unique values and their counts, it automatically excludes blank cells from its result. However, when combining it with COUNTIF, make sure to handle blanks explicitly: =IF(cell_reference<>"", COUNTIF(data, cell_reference), "") This way, blanks won’t clutter your results. If you’re using Excel 365 or Excel 2019, the FILTER function is a powerful tool for excluding blanks before any calculation. For example: =FILTER(data, data<>"") This creates a clean range without blanks, which can then be used in further formulas like COUNTIF or SUM. Ignoring blanks ensures your results are accurate and your formulas don’t inadvertently count empty cells as meaningful entries. For example, if you’re calculating the frequency or proportion, including blank cells could skew your data and lead to misleading interpretations. I hope this helps! If you’d like a specific example tailored to your dataset, feel free to ask-I’d be happy to assist further.

Thanks for your feedback!

Please try this solution, it uses regular Excel functions only: exceldashboardschool.com/count-unique-text-values-in-a-range/ UTEXT is a part of our free function library: exceldashboardschool.com/custom-functions-add-in/

exceldashboardschool.com/custom-functions-add-in/

Hi, I'll check it. By default the "Keep source formulas in Outputs" checkbox is active. (Step 4)

Please check this: kzbin.info/www/bejne/rInPan6eZ9iJipY

Hi, if you have an installed DataXL add-in, locate the Worksheets Tab and choose the "Split Sheets to Files" command.

@@excelkid that saves the existing multiple sheets as separate files. I have one sheet in my workbook that i need to split into separate workbooks/files. According to this video, Splitter has the option to do so, although he split the table into sheets within the same workbook. But the option was there in the Splitter window. With DataXL, you need to split your data into sheets then split the sheets into separate files. Splitter is much easier. So, is splitter still available to download? Thanks

Please check it: exceldashboardschool.com/split-worksheet-into-multiple-sheets-based-on-column/

Hi, we uploaded a tool for splitting purposes: kzbin.info/www/bejne/rInPan6eZ9iJipY

exceldashboardschool.com/free-excel-add-ins-tools/

exceldashboardschool.com/free-excel-add-ins-tools/

@@excelkid I downloaded that file, but DataFX was added, but DataXL was not. how do I do?

Hi, we are talking about two different tools. DataXL is a productivity tool, while DataFX is a function library. You can download and install them simultaneously.

The function is a part of our custom library, it is free. exceldashboardschool.com/free-excel-add-ins-tools/

Hi, excuse me but can i see how many times are repeated? I just see the list that it's created but no how many? Please, can you explain it in detail how. Thank you...

Thank you for your question! It's a common point of confusion, so let me break it down for you step by step and explain exactly how the solution in the video handles not just creating a list of unique items but also calculating how many times each item is repeated. First, the formula used in the "Frequency" column (e.g., =1/COUNTIF(data,data)) may seem a bit abstract, so here's what's happening: Identifying Repetition: The COUNTIF(data, data) part calculates how many times each value appears in the dataset. For example, if "10" appears 4 times, COUNTIF will return 4 for each occurrence of "10." Calculating Proportions: By taking 1/COUNTIF(data, data), we invert this count to calculate the "proportion" of each instance within the dataset. So, if "10" appears 4 times, each occurrence contributes 1/4 = 0.25 to the overall calculation. Summing Unique Values: In the column labeled "Count Unique," the formula =SUM(1/COUNTIF(data, data)) sums these proportions, effectively counting how many unique values exist in the dataset. Now, if your goal is simply to see how many times each value is repeated (rather than its proportion), you can use the COUNTIF function directly: Add a new column titled "Repetitions." Use the formula =COUNTIF(data, cell_reference) where data is your range and cell_reference is the cell with the value you want to count. For instance, if "10" appears 4 times, this formula will return "4." This way, you’ll have a clear view of the count of each unique value in your dataset. Lastly, if you’re working with newer versions of Excel, you could combine UNIQUE with COUNTIF to create a dynamic table of unique values and their respective counts: Use =UNIQUE(data) to create a list of unique values. In the next column, use =COUNTIF(data, cell_reference_from_unique_column) to calculate how many times each unique value appears. I hope this clears things up for you! If you need further explanation or want me to walk you through a specific example, feel free to ask-I’m happy to help!

Hello! Thank you for sharing your thoughts and preference for the UNIQUE function-it’s indeed a fantastic tool introduced in recent Excel versions. However, it seems there might be a misunderstanding about the context and purpose of the solution shown in this video, so allow me to clarify why the COUNTIF-based method might be more appropriate in certain scenarios. The video specifically demonstrates how to calculate the frequency of each unique value in a dataset, which is quite different from merely listing unique values. While the UNIQUE function is excellent for generating a distinct list of items, it does not inherently provide a way to compute their frequency or proportion relative to the entire dataset. For instance, the formula =SUM(1/COUNTIF(data,data)) not only counts unique values but also helps derive the proportion or "frequency share" of each item-a capability not directly supported by UNIQUE without combining it with additional formulas. Another consideration is compatibility. The UNIQUE function is part of the newer dynamic array functions. If someone is working on an older version of Excel, the COUNTIF approach remains a universally compatible and highly flexible alternative. Lastly, while UNIQUE may appear simpler in some contexts, its functionality doesn't cover the same breadth as the formula used in this video. The COUNTIF approach elegantly combines multiple tasks-counting unique values, calculating frequencies, and creating dynamic results-all in a single formula, making it particularly suitable for more advanced or nuanced data analysis. In summary, the UNIQUE function is excellent for quickly isolating distinct values in modern Excel environments, but it's not a direct substitute for the solution shown here. Different tools have different strengths, and understanding when to use each is key to maximizing Excel's potential. Hope this clears up the confusion and helps you see the value of both methods! Let me know if you have further questions-I’d be happy to discuss more Excel tips!

exceldashboardschool.com/if-cell-contains-specific-text/

I am also facing same challange