Forum Discussion
Sorting numbers
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...
Here's my solution:
=SUM((matrix<{4;1;2})+(matrix>{8;5;6}))Matrix being the designated range in the sheet.
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.
ah dang that is how. I tried SUM(BYCOL(m, PRODUCT(m))) and other variations and kept getting errors. so simple... lol. this is why I love participating here, you learn something new all the time :)
That is correct that there are no numbers above 13 and no negative numbers. If you look you will find that every column have the sum of 13.
May I ask how you define/asign the matrix part? When I try to type your formulas I get "name?"-error
It would have been nice if you could have provided a data set for people to work with. Preferably with a defined name of your choice to identify the range of the data to be analysed. Since you had no suggestions as to the meaning of the data Matt and others have called it 'matrix'. The defined name should be applied using Name Manager or the Name box to the left of the function bar.
I certainly hadn't noticed the column sums were all 13.
"dang" is a mild expletive, an expression of frustration.
I guess the goal is not crystal clear. I took an educated guess!
This looks promising.
But I want only to count the columns that have no red mark. So in your exampel the count is 0. In my first picture the count is 2. In my picture below there are nine columns that are not marked in any of the three rows.
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...
Thank you! Thank you! Thank you! This functions do the work
