Stacked Excel Formula

Hello everyone. I've spent the past 2-3 hours trying to figure this out on my own without luck. What I'm needing is a formula that will check D11 (highlighted) to make sure that it's within the parameters listed below it (<17), then I need it to do the same for H11 and I11 (highlighted) and enter the number (1-3) that are not "equal to or greater/less than" into K11. K11 reflects how many samples in Row 11 that are outside of those parameters. I'm trying to make this worksheet more automated and this is the one thing that I cant figure out.

Example 1: (all numbers are within parameters, so a 0 is entered into column K)

Example 2: (column G is not within the parameters, therefor there is 1 entry in column K)

excel

Formulas and Functions

IlirU

Feb 10, 2026

Hi tsbarton70,

I created below formula a few days ago but I was very hesitant whether I should post it or not, as it is a very large and difficult formula to understand, but in the end I decided to post it today. If this formula does not serve to you, or in real conditions it does not give the right results, then let me know and I will delete this post.

=IFNA(TOCOL(EXPAND(BYROW(--TRANSPOSE(TEXTSPLIT(TEXTJOIN(";",,
      BYCOL(B2:G13, LAMBDA(x, ARRAYTOTEXT(LET(d, SUBSTITUTE(x, " ", ""),
rw, ROWS(d), mod, MOD(SEQUENCE(rw), 2), br, BYROW(IF(d = "", 0, d),
      LAMBDA(a, TRIM(REGEXREPLACE(REGEXREPLACE(SUBSTITUTE(SUBSTITUTE(a, ">", ">"), "<", "<"),
      "[^0-9.,+\-*/^()≤≥<>=% ]", " "), "\s+", " ")))),
tb, IFERROR(TEXTBEFORE(br, " ", LEN(br) - LEN((SUBSTITUTE(br, " ", "")))), br),
op, FILTER(tb, mod = 0), LET(a, --SUBSTITUTE(--FILTER(tb, mod = 1), ",", "."),
 b, --SUBSTITUTE(IFERROR(--BYROW(op, LAMBDA(y,
      IFERROR(REGEXEXTRACT(TRIM(SUBSTITUTE(SUBSTITUTE(y, ">", ">"), "<", "<")),
      "[0-9]+(?:[.,][0-9]+)?"), ""))), 0), ",", "."),
op, XLOOKUP(BYROW(op, LAMBDA(z, IFERROR(REGEXEXTRACT(TRIM(SUBSTITUTE(SUBSTITUTE(z, ">", ">"), "<", "<")),
      "(>=|<=|≥|≤|>|<|=)"), ""))), {"<","≤","=","≥",">"}, {"<","<=","=",">=",">"}),
      IFNA(IF(SWITCH(op, ">=", a >= b, "<=", a <= b, ">", a > b, "<", a < b, "=", a = b, NA()), 0, 1), 0))))))), ", ", ";")), SUM),, 2)), "")

Hope this helps.

IlirU

8 Replies

tsbarton70
Copper Contributor
Mar 05, 2026
Thank you all for the help and (again) I apologize for taking so long on marking a solution. I appreciate each and every one of you.
Lorenzo
Silver Contributor
Feb 20, 2026
Hi tsbarton70
We haven't heard from you since day 1. Is this issue sorted out? Did any of the proposal you got did it?
At the bottom of each reply you got there's a Mark as solution link ==> Helps people who Search for existing solutions
Thanks
- tsbarton70
  Copper Contributor
  Mar 05, 2026
  I am so sorry. It got hectic on my end (medical issues/state inspections) so I haven't tried the solutions yet. Since things have now calmed down, I will be working on it today. I will update everything once it's completed.

IlirU

Iron Contributor

Feb 10, 2026

Hi tsbarton70,

=IFNA(TOCOL(EXPAND(BYROW(--TRANSPOSE(TEXTSPLIT(TEXTJOIN(";",,
      BYCOL(B2:G13, LAMBDA(x, ARRAYTOTEXT(LET(d, SUBSTITUTE(x, " ", ""),
rw, ROWS(d), mod, MOD(SEQUENCE(rw), 2), br, BYROW(IF(d = "", 0, d),
      LAMBDA(a, TRIM(REGEXREPLACE(REGEXREPLACE(SUBSTITUTE(SUBSTITUTE(a, ">", ">"), "<", "<"),
      "[^0-9.,+\-*/^()≤≥<>=% ]", " "), "\s+", " ")))),
tb, IFERROR(TEXTBEFORE(br, " ", LEN(br) - LEN((SUBSTITUTE(br, " ", "")))), br),
op, FILTER(tb, mod = 0), LET(a, --SUBSTITUTE(--FILTER(tb, mod = 1), ",", "."),
 b, --SUBSTITUTE(IFERROR(--BYROW(op, LAMBDA(y,
      IFERROR(REGEXEXTRACT(TRIM(SUBSTITUTE(SUBSTITUTE(y, ">", ">"), "<", "<")),
      "[0-9]+(?:[.,][0-9]+)?"), ""))), 0), ",", "."),
op, XLOOKUP(BYROW(op, LAMBDA(z, IFERROR(REGEXEXTRACT(TRIM(SUBSTITUTE(SUBSTITUTE(z, ">", ">"), "<", "<")),
      "(>=|<=|≥|≤|>|<|=)"), ""))), {"<","≤","=","≥",">"}, {"<","<=","=",">=",">"}),
      IFNA(IF(SWITCH(op, ">=", a >= b, "<=", a <= b, ">", a > b, "<", a < b, "=", a = b, NA()), 0, 1), 0))))))), ", ", ";")), SUM),, 2)), "")

Hope this helps.

IlirU

Lorenzo
Silver Contributor
Feb 20, 2026
Hey IlirU
(It's been a while...). I haven't tried to "debug" your formula but think I saw somewhere:
IFERROR( TEXTBEFORE(br, " ", LEN(br) - LEN( SUBSTITUTE(br, " ", "") ) ), br )
TEXTBEFORE last arg. - [if_not_found] - could be used instead:
TEXTBEFORE(br, " ", LEN(br) - LEN( SUBSTITUTE(br, " ", "") ),,, br)
Cheers
Lz.
- IlirU
  Iron Contributor
  Feb 20, 2026
  Thank you Lorenzo, for pointing this out.
  I appreciate it.
  IlirU

Lorenzo

Silver Contributor

Feb 06, 2026

(initial post edited - due credit m_tarler)

in H11:

=OUT_PARAMETER( D11:D12, H11:H12, I11:I12 )

where OUT_PARAMETER is LAMBDA:

=LAMBDA([ref_1],[ref_2],[ref_3],
  3 - SUM(
        IF(ISOMITTED(ref_1), 1, COMPARE_TOPARAMETER(ref_1)),
        IF(ISOMITTED(ref_2), 1, COMPARE_TOPARAMETER(ref_2)),
        IF(ISOMITTED(ref_3), 1, COMPARE_TOPARAMETER(ref_3))
      )
)

and COMPARE_TOPARAMETER:

=LAMBDA(reference,
  LET(
    RefValue,     TAKE( reference, 1, 1 ),
    comparerStr,  TAKE( reference, -1, 1 ),
    IF(
        comparerStr = 0, 1,
        LET(
            Operator,       TRIM(TEXTBEFORE(comparerStr, {0, 1, 2, 3, 4, 5, 6, 7, 8, 9})),
            afterOperator,  TRIM(TEXTAFTER(comparerStr, Operator)),
            ComparerValue,  VALUE(TEXTBEFORE(afterOperator, " ", , , , afterOperator)),
            --CHOOSE(
                XMATCH(Operator, {"<", "≤", "=", "≥", ">"}),
                RefValue < ComparerValue,
                RefValue <= ComparerValue,
                RefValue = ComparerValue,
                RefValue >= ComparerValue,
                RefValue > ComparerValue
            )
        )
    )
  )
)

LAMBDAs are stored in Name Manager of the attached wbook

OutParamater.xlsx14 KB

m_tarler
Silver Contributor
Feb 06, 2026
So the problem is you need to actually write a simple parser to interpret that second line. Here is an example that can do that:
=LET(in,D11:I12, checks, BYCOL(in,LAMBDA(c, IF(N(INDEX(c,1)),LET( val, INDEX(c,1), crit, INDEX(c,2), compare, SWITCH(LEFT(crit,1), "<",-2,"≤",-1,"=",0,"≥",1,">",2,3), value, --MID(crit,2,SEARCH(" ",crit)-2), --CHOOSE(compare+3, val>=value, val>value, val<>value, val<value, val<=value, 0)),0))), SUM(checks))
Alternatively you can take the above code and make a LAMBDA function out of it so each cell in K would need a very simple formula. So lets call it "FAILS". So in the Name Manager (on the 'Formulas' ribbon) click NEW and then enter FAILS as the name and then the following formula in the Refers To: box
=LAMBDA(in, SUM(BYCOL(in,LAMBDA(c, IF(N(INDEX(c,1)),LET( val, INDEX(c,1), crit, INDEX(c,2), compare, SWITCH(LEFT(crit,1), "<",-2,"≤",-1,"=",0,"≥",1,">",2,3), value, --MID(crit,2,SEARCH(" ",crit)-2), --CHOOSE(compare+3, val>=value, val>value, val<>value, val<value, val<=value, 0)),0)))))
it is essentially the same as the above but not defining that range D11:I12 and will now let you enter it like a formula. so now in K11 you just enter:
=FAILS(D11:I12)
and voila

Forum Discussion

Stacked Excel Formula