Forum Discussion

Copper Contributor

Jan 23, 2025

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

excel

Formulas and Functions

djclements
Jan 24, 2025
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...
count_not_marked_columns.xlsx12 KB

m_tarler

Bronze Contributor

Jan 23, 2025

first off your rules and your image do not match. That said I believe a rule using SUMPRODUCT like below will work for you:

=LET(in,A11:T13, 
          r_1, TAKE(in,1), r_2, CHOOSEROWS(in,2), r_3, TAKE(in, -1),
          SUMPRODUCT(
                (r_1<=3)+(r_1>=9)*(r_1<=13),
                (r_2=0)+(r_2>=6)*(r_2<=13),
                (r_3<=1)+(r_3>=7)*(r_3<=13)
           )
     )

rows 1 & 2 just define the 3 rows

rows 4-6 define the rules for each row respectively

and then the sumproduct will multiply each element together and add the result