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.

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/

I'll check it!

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))

Thanks! :)

Hi, the function is a part of our free add-in. Please check the link in the description.

Hi, using AND instead of * would not produce a valid multiplication result. AND is for logical tests; * is for arithmetic multiplication. AND returns a boolean (TRUE or FALSE); * returns a numeric result.

I am getting an error when I try to download it. It says can't view in protected view?

Hi, please right-click on the file. At the bottom of the General tab, select the Unblock checkbox and select OK.

I don't see that option. I think my organization has blocked add ins :(

???

You can combine multiple conditions using the IF function nested within each other. Here's how you can do it: =IF(A1="CANCELLED","CANCELLED",IF(TODAY()<A1,"PENDING",IF(TODAY()>=B1,"SEND RENEW REMINDER","WAIT"))) This formula first checks if A1 is "CANCELLED". If it is, it returns "CANCELLED". If not, it proceeds to check if TODAY's date is less than A1's date. If true, it returns "PENDING". If false, it checks if TODAY's date is greater than or equal to B1's date, returning "SEND RENEW REMINDER" if true, and "WAIT" if false.

@@excelkid Thank you very much. I have applied the same formula, with little changes and my purpose is solved. Thanks for your guidance. Means a lot. Santosh Pillai 😊✌🤝🌹🌹

The function is a part of a free library. The main goal is to improve the built-in functions:excelkid.com/add-ins/#datafx-function-library-udf-formulas

The function is a part of a free library. The main goal is to improve the built-in functions:excelkid.com/add-ins/#datafx-function-library-udf-formulas

Please send us a message: [email protected]

Hi only one add-in is needed: excelkid.com/add-ins/#datafx-function-library-udf-formulas

Hi, please contact us: [email protected]

The function is a part of a free library. The main goal is to improve the built-in functions:excelkid.com/add-ins/#datafx-function-library-udf-formulas

Thanks for your comment!

Hi, we'll publish an update soon.