Forum Discussion
Solved
Sorting numbers
Hi, I have a excel sheet with x number of columns. I have formated the rows so that if there is a specific number it is marked in red. See picture below. The numbers that are marked are Top row ...
The formula needs to match the logic used in your Conditional Formatting rules. Based on your screenshots, I believe you meant to say the rule for the Top Row is "0 - 2 and 9 - 13. So, numbers 3-8 shall not be marked".
Also, I think the logic just needs to be reversed from what's already been suggested. Try either:
=SUM(N(NOT(BYCOL((A1:AI3<{3;1;2})+(A1:AI3>{8;5;6}),OR))))Or:
=SUM(BYCOL((A1:AI3>{2;0;1})*(A1:AI3<{9;6;7}),PRODUCT))See attached...
I like your approach but I believe the whole column must meet the criteria to be counted so I would propose:
=SUM(--(MMULT({1,1,1},(matrix<{4;1;2})+(matrix>{8;5;6}))=3))or
=LET(m,(matrix<{4;1;2})+(matrix>{8;5;6}),
SUMPRODUCT(CHOOSEROWS(m,1),CHOOSEROWS(m,2),CHOOSEROWS(m,3)))or
=LET(m,(matrix<{4;1;2})+(matrix>{8;5;6}),
SUM(BYCOL(m,LAMBDA(c,PRODUCT(c)))))or I'm sure there are more options.
That all said these options assume 13 is a max and therefore the <=13 is not needed and both this and my option both assume no negative numbers.
I would go with the last one
=LET(
m, (matrix<{4;1;2})+(matrix>{8;5;6}),
SUM(BYCOL(m, PRODUCT))
)but with an eta-reduced function.
