Thanks for your feedback!

Hi, thanks for your feedback. We'll check it and try to optimize / improve the code.

Thank you for your feedback!

Hello, thank you for your comment! I considered going into the code analysis, but ultimately decided on the shorter version because 90% of the viewers are only interested in the final solution (not so much in how the code exactly works). I'll upload a version here that explains how the function works. Explanation of the Function: Function SUMBYCOLOR(color As Range, ParamArray ranges() As Variant) As Double ' Declare variables to be used within the function Dim cell As Range Dim targetRange As Variant Dim sumResult As Double ' Initialize the sum result to 0 sumResult = 0 ' Loop through each target range in the ParamArray ranges For Each targetRange In ranges ' Check if the current item in ranges is of type "Range" If TypeName(targetRange) = "Range" Then ' Loop through each cell in the current target range For Each cell In targetRange ' Check if the background color of the current cell matches the reference color If cell.Interior.color = color.Interior.color Then ' If the colors match, add the cell's value to sumResult sumResult = sumResult + cell.Value End If Next cell End If Next targetRange ' Assign the final sumResult to the function's return value SUMBYCOLOR = sumResult End Function

@@excelkid thanks for the reply. VBA code correct

Thank you!

Looks great!

Thanks!

Hi! Currently, the new checkboxes feature is only available for a portion of the Beta channel of Microsoft 365, but hopefully, it will roll out to the masses soon.

If you have 3 lists (B3:B9, C3:C9 and D3:D9), the formula is the following: =UNIQUE(FILTER(B3:B9, (COUNTIF(C3:C9, B3:B9) > 0) * (COUNTIF(D3:D9, B3:B9) > 0))) Explanation: COUNTIF(C3:C9, B3:B9) > 0: This part checks if each value in the range B3:B9 (List 1) is present in the range C3:C9 (List 2). COUNTIF(C3:C9, B3:B9) returns an array of counts indicating how many times each value in B3:B9 appears in C3:C9. > 0 converts this array to TRUE or FALSE values, where TRUE means the value is present in List 2. COUNTIF(D3:D9, B3:B9) > 0: Similar to the first part, this checks if each value in B3:B9 is present in D3:D9 (List 3). COUNTIF(D3:D9, B3:B9) returns an array of counts indicating how many times each value in B3:B9 appears in D3:D9. > 0 converts this array to TRUE or FALSE values, where TRUE means the value is present in List 3. Multiplying the Conditions: (COUNTIF(C3:C9, B3:B9) > 0) * (COUNTIF(D3:D9, B3:B9) > 0): This multiplication of boolean arrays (TRUE or FALSE values) performs an AND operation. Only if a value is TRUE in both arrays (i.e., present in both List 2 and List 3), the result will be TRUE. Otherwise, it will be FALSE. FILTER(B3:B9, ...): The FILTER function takes an array (in this case, B3:B9) and a condition array (the result of the multiplication above). It returns an array of values from B3:B9 where the condition is TRUE. UNIQUE(...): The UNIQUE function takes an array (the result from FILTER) and returns an array of unique values, removing any duplicates. The formula =UNIQUE(FILTER(B3:B9, (COUNTIF(C3:C9, B3:B9) > 0) * (COUNTIF(D3:D9, B3:B9) > 0))): Checks if values in List 1 (B3:B9) are present in both List 2 (C3:C9) and List 3 (D3:D9). Filters the values in List 1 to include only those that are present in both List 2 and List 3. Returns only the unique values from the filtered list.

Hi, download link: excelkid.com/add-ins/

@@excelkid Thank you so much 👍It is really great 😊

Hi, excelkid.com/add-ins/

Hi, it will be published soon: (MS website) Availability   ------------------ These functions are currently available to Beta Channel users running: Windows: Version 2406 (Build 17715.20000) or later Mac: Version 16.86 (Build 24051422) or later Don’t have it yet? It’s probably us, not you. Features are released over some time to ensure things are working smoothly. We highlight features that you may not have because they’re slowly releasing to larger numbers of Insiders. Sometimes we remove elements to further improve them based on your feedback. Though this is rare, we also reserve the option to pull a feature entirely out of the product, even if you, as an Insider, have had the opportunity to try it.

@@excelkid thankyou ❤️

Hi, download the add-in (zip package). excelkid.com/add-ins/#dataxl-productivity-suite Then install the add-in: excelkid.com/how-to-install-an-excel-add-in/

Hello, yes, unfortunately, it currently only works on Windows. But we are continuously looking for a solution to make it usable on Mac as well.

its always sequential, you'd have to sort that way out urself

support.microsoft.com/en-us/office/scan-function-d58dfd11-9969-4439-b2dc-e7062724de29

Hi, SUMNTH function is a part of our free DataFX function library contains 200+ high-performance user-defined functions (UDFs), each designed to simplify complex tasks and transform your experience with Excel. excelkid.com/add-ins/ But it is possible to perform the calculation (without using an add-in) using these methods: Method1: SUMPRODUCT If your values are in column A, for example in the range A1:A100, and you want to sum every 3rd value, you can use the following formula: =SUMPRODUCT((MOD(ROW(A1:A100)-ROW(A1), 3)=0)*A1:A100) Method 2: Using INDEX and SUM Another approach is to use INDEX and SUM. Assume your values are in the same range (A1:A100), and you want to sum every 3rd value: =SUM(INDEX(A1:A100, ROW(INDIRECT("1:" & INT(COUNTA(A1:A100)/3)))*3-2)) Method 3: Using OFFSET Another solution involves using the OFFSET function. Let's assume your values are still in the range A1:A100, and you want to sum every 3rd value: =SUMPRODUCT(--(MOD(ROW(A1:A100)-ROW(A1), 3)=0), A1:A100) You can also use the OFFSET function in a similar manner: =SUM(OFFSET(A$1, (ROW(INDIRECT("1:" & INT(COUNTA(A1:A100)/3)))-1)*3, 0))

Hi, currently the add-in works with Microsoft Excel.

ty!

Thank you for your comment, I'm glad you shared your opinion.

Thanks for your feedback :)

Hi, this function is a part of our free DataFX function library contains 200+ high-performance user-defined functions (UDFs), each designed to simplify complex tasks and transform your experience with Excel. excelkid.com/add-ins/

Thanks for the feedback!

The COUNTBYCOLOR function is only available in our free function library. excelkid.com/add-ins/#datafx-function-library-udf-formulas

Hi, thank you for the option, it worked in my computer, the only thing is that, when I save the file, close the excel, and open again, the Dx.. under Developer disappear and I need to add again. Is there a way to keep it after I close excel & open? Thanks!!

We have just updated the add-in to version 1.61, please check the functions. The situation is not easy at all right now because the functions are currently available only in the Excel Insider program, and it is possible - it has happened before - that something changes along the way. We strive to provide 100% compatibility because not everyone uses the subscription-based Microsoft 365 model. I think I will write a long-form article on my website today to gather the regex formulas and downloads in one central place.

Hi, we try to find an easy way to solve complex tasks. excelkid.com/add-ins/#datafx-function-library-udf-formulas

The TEXTSPLIT function is only available in Excel for Microsoft 365 (Windows and Mac) and Excel for the web. You can use a custom function, like SUMCSV. excelkid.com/add-ins/#datafx-function-library-udf-formulas

Hi, please send your Workbook to [email protected]. We'll check it!

@@excelkid I also can't get it to work, despite following the pdf guides. I created a new file and tried the ExtractDate function but it always gives No dates found. I am using Excel Pro Plus 2021 Version 2108 (Build 14332.20706)

Unfortunately, it seems that not all date formats are supported. We will work on it.

Hi, this function is a part of our free DataFX function library contains 200+ high-performance user-defined functions (UDFs), each designed to simplify complex tasks and transform your experience with Excel. excelkid.com/add-ins/

Thank you very much for the useful solution.

FILTER Function: Syntax: FILTER(array, include, [if_empty]) Usage: This function returns an array of values from the array argument that meet the criteria specified in the include argument. In this formula: FILTER(B3:B9, COUNTIF(C3:C9, B3:B9)), the array is B3:B9, meaning it will filter values from this range. COUNTIF Function: Syntax: COUNTIF(range, criteria) Usage: This function counts the number of cells in the specified range that meet a given criteria. In this formula: COUNTIF(C3:C9, B3:B9): range is C3:C9. criteria is each individual value in B3:B9. Step-by-Step Explanation: Evaluate COUNTIF(C3:C9, B3:B9): The function checks each value in B3:B9 against the values in C3:C9 and counts how many times each value from List 1 appears in List 2. Results for COUNTIF: Orange (B3) in C3:C9: 0 occurrences. Kiwi (B4) in C3:C9: 1 occurrence. Lime (B5) in C3:C9: 1 occurrence. Cherry (B6) in C3:C9: 1 occurrence. Strawberry (B7) in C3:C9: 0 occurrences. Melon (B8) in C3:C9: 1 occurrence. Mango (B9) in C3:C9: 0 occurrences. Filter Based on COUNTIF Results: The FILTER function uses the results of COUNTIF to determine which values in B3:B9 to include in the output. The include argument in FILTER is an array of Boolean values (true or false) based on whether COUNTIF results are greater than 0. Specifically: Orange is not included (0). Kiwi is included (1). Lime is included (1). Cherry is included (1). Strawberry is not included (0). Melon is included (1). Mango is not included (0). Result in Column G: The FILTER function returns the values from B3:B9 where the corresponding COUNTIF result is greater than 0. The values shown in Column G are: Kiwi, Lime, Cherry, and Melon. Summary The formula =FILTER(B3:B9, COUNTIF(C3:C9, B3:B9)) filters and displays the values from List 1 (Column B) that also appear in List 2 (Column C). It achieves this by using the COUNTIF function to count occurrences of each item from List 1 in List 2, and the FILTER function to include only those items that are found at least once in List 2. The final filtered list is displayed in Column G, showing the common values between the two lists: Kiwi, Lime, Cherry, and Melon.

Thanks a lot for taking the time out and explaining

Hi, thanks for the comment. Only a fraction of Microsoft Add-ins are found in the store because they use JavaScript. Our free add-in contains 200+ UDFs and is 100% based on VBA code. It is fast and compatible with all versions. It would be quite embarrassing if a channel of this size released unclean code :) The add-in is called DataFX, and we have been developing it for about 3 years. You can download it here: excelkid.com/add-ins/#datafx-function-library-udf-formulas To dispel any doubts, I will also post the source code of the applied functions here. Well, there are 200+ functions like this. Function COMPARE(rng1 As Range, rng2 As Range, Optional ReturnType As Integer = 0) As Variant On Error GoTo ErrHandler Dim outputValues() As Variant Dim i As Long, j As Long, k As Long Dim currentValue As Variant, otherValue As Variant Dim isFound As Boolean ' Check for null ranges If rng1 Is Nothing Or rng2 Is Nothing Then COMPARE = CVErr(xlErrRef) Exit Function End If ' Initialize array to maximum possible size ReDim outputValues(1 To Application.Max(rng1.Cells.count, rng2.Cells.count)) Dim index As Long index = 1 Select Case ReturnType Case 1 ' Values in rng1 not in rng2 For i = 1 To rng1.Cells.count currentValue = rng1.Cells(i).Value isFound = False For j = 1 To rng2.Cells.count otherValue = rng2.Cells(j).Value If currentValue = otherValue Then isFound = True Exit For End If Next j If Not isFound Then outputValues(index) = currentValue index = index + 1 End If Next i Case 2 ' Values in rng2 not in rng1 For i = 1 To rng2.Cells.count currentValue = rng2.Cells(i).Value isFound = False For j = 1 To rng1.Cells.count otherValue = rng1.Cells(j).Value If currentValue = otherValue Then isFound = True Exit For End If Next j If Not isFound Then outputValues(index) = currentValue index = index + 1 End If Next i Case Else ' Common values For i = 1 To rng1.Cells.count currentValue = rng1.Cells(i).Value For j = 1 To rng2.Cells.count otherValue = rng2.Cells(j).Value If currentValue = otherValue Then isFound = False For k = 1 To index - 1 If outputValues(k) = currentValue Then isFound = True Exit For End If Next k If Not isFound Then outputValues(index) = currentValue index = index + 1 End If End If Next j Next i End Select ' Check if no matches were found If index = 1 Then ReDim outputValues(1 To 1) outputValues(1) = "Not Found" Else ReDim Preserve outputValues(1 To index - 1) End If ' Transpose for column output COMPARE = Application.Transpose(outputValues) Exit Function ErrHandler: COMPARE = CVErr(xlErrValue) End Function

Direct Download link: excelkid.com/wp-content/uploads/2024/04/DFX149.zip

Hi, this function is a part of our free DataFX function library contains 200+ high-performance user-defined functions (UDFs), each designed to simplify complex tasks and transform your experience with Excel. excelkid.com/add-ins/

Hi, The built-in Excel functions do not support operations with colored cells. This function is a part of our free DataFX function library contains 200+ high-performance user-defined functions (UDFs), each designed to simplify complex tasks and transform your experience with Excel. excelkid.com/add-ins/