Forum Discussion
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
0 - 3 and 9 - 13. So numbers 4-8 shall not be marked - Middle row
0 and 6 - 13. So numbers 1-5 shall not be marked - Bottom row
0 - 1 and 7 - 13. So numbers 2-6 shall not be marked
What I now whant is to count all columns that are not marked red.
As of now I do this manual with marking with "1" below and use function Count.
Someone that can help me with a function for this?
Most thankful.
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...
To play with OfficeScript, number of columns for which no one cell is marked by color
function main(workbook: ExcelScript.Workbook) { const data = workbook.getNamedItem("data").getRange() const nColumns = data.getColumnCount() const notFilled = "#FFFFFF" const columns = Array.from(Array(nColumns).keys()) const count = columns .reduce((a, v) => a + Number((data.getColumn(v).getFormat().getFill().getColor() == notFilled)), 0) data.getLastCell().getOffsetRange(0, 2).setValue("Not marked " + count) }Thank you Sergei,
Your code seems to accually do the job I am looking for. Nest step is to implement it in my sheet. Any possibillity to help me how to do that?
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.
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...
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
Thank you, first you are right. For row 1 0-2 and 9-13 shall be marked red.
Second I am not sure this does it, at least I don´t understand how 😊.
What I want is to count the number of columns that has no red markings, or in other words
- row 1 have a number that is between 3-8 AND/OR
- row 2 have a number that is between 1 -6 AND/OR
- row 3 have a number that is between 2-6
Do I make myself clear?
