+ Reply to Thread
Results 1 to 6 of 6

using LEN formula in minifs,sumifs, averageifs formulas

  1. #1
    Registered User
    Join Date
    10-09-2011
    Location
    Kosice, Slovakia
    MS-Off Ver
    office 365 / excel 2021
    Posts
    20

    using LEN formula in minifs,sumifs, averageifs formulas

    Hi,

    I would like to ask you for help with a MINIFS formula where one of the conditions is the length of words in the criteria range.

    e.g I need to calculate the minimum value in range A2:E5 where one of the criteria is that the length of text in range A1:E1 is 4 . In this case, it is ( B1 and E1) and the result is -1 from B2

    A B C D E
    1 ABCDE ABCD ADF AB ABCD
    2 -5 -1 2 1 3

    I want to use the lenght formula in minifs formula
    something like =minifs (A2:E2;A1:E1;len(A1:E1)=4) or =minifs (A2:E2;A1:E1;"len(A1:E1)=4") or =minifs (A2:E2;A1:E1;"len(A1:E1)="&4)

    unfortunately, none of these is working for me. I do not know how to write correctly the criterium for the criterium rage.

    I know how to do it for simple criteria like =; >,"" or e.g. for minimum where the criterium is the appearance of letter "C" in the criteria range. In this case, it is A2/B2/E2 and the result is -5 =minifs(A2:E2;A1:E1;"*C"). Unfortunately, I do not know ow to use the formula LEN in the criteria.
    Last edited by slastanrado; 01-23-2021 at 04:34 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,401

    Re: using LEN formula in minifs,sumifs, averageifs formulas

    MINIFS is not available in XL2007, which your profile states you are using, so please update this in User CP if it is out of date.

    With the xxxIF(S) functions, you cannot apply functions to the criteria range. You can use the array* construct MIN(IF(... , like this:

    =MIN(IF(LEN(A$1:E$1)=4,A2:E2))

    *Note that an array formula needs to be confirmed using the key combination Ctrl-Shift-Enter instead of the usual Enter, unless you are using Excel 365.

    Hope this helps.

    Pete

    EDIT: You will probably have to use semicolons ( ; ) instead of commas ( , ) in the formula, depending on your regional settings.
    Last edited by Pete_UK; 01-23-2021 at 04:10 PM.

  3. #3
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    3,179

    Re: using LEN formula in minifs,sumifs, averageifs formulas

    ---Deleted by user----

  4. #4
    Registered User
    Join Date
    10-09-2011
    Location
    Kosice, Slovakia
    MS-Off Ver
    office 365 / excel 2021
    Posts
    20

    Re: using LEN formula in minifs,sumifs, averageifs formulas

    Hi, I am using office 365. Unfortunately, I am not very familiar with CSE functionality, so probably I will have to start with learning how to use it.
    Is there any chance to use the single formula without CTRL-SHIFT-Enter? e.g by using sumproduct formula?

    It looks like your formula is working if there is only one condition but I am not able to add another criterion by IF(AND) function

    I need to add a criteria A2:E2>0
    Last edited by slastanrado; 01-23-2021 at 05:50 PM.

  5. #5
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: using LEN formula in minifs,sumifs, averageifs formulas

    Maybe

    =MINIFS(A2:E2;A$1:E$1;REPT("?";4))

  6. #6
    Registered User
    Join Date
    10-09-2011
    Location
    Kosice, Slovakia
    MS-Off Ver
    office 365 / excel 2021
    Posts
    20

    Re: using LEN formula in minifs,sumifs, averageifs formulas

    Quote Originally Posted by hrlngrv View Post
    Maybe

    =MINIFS(A2:E2;A$1:E$1;REPT("?";4))
    It Works. Thanks a lot. I can easily add criterium for A1:E1>0

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Month-by-month nested Sumifs and AverageIfs formulas stopped working
    By Loyo123 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-16-2020, 07:41 PM
  2. SUMIFS()/12 or AVERAGEIFS() formula by DD from YYYY-MM-DD
    By Perlapimpim in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-23-2019, 05:57 AM
  3. Replies: 3
    Last Post: 07-24-2019, 11:59 PM
  4. Minifs, maxifs, averageifs, medianifs, coeffvarifs
    By qwertyjjj in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-30-2014, 07:48 PM
  5. [SOLVED] Complex if Formulas AVERAGEIFS, SUMIFS, COUNTIFS
    By fredlo2010 in forum Excel General
    Replies: 5
    Last Post: 06-19-2014, 03:26 PM
  6. [SOLVED] AverageIFs / SumIfs
    By Decar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-09-2013, 02:59 PM
  7. Averageifs formula with criteria as formulas
    By KCD in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-07-2013, 05:34 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1