Numbers won't be arranged correctly by filter in table.

[isafloor]

Hello, I have two tables one with data and the other a summary of the data.

The data table includes in column A a code for a week of the year (202101, for example for year 2021 and week 01), column B the name of workers, and column C the absences in the workweek.

The summary table finds the combination of name+weekcode and if there is data (0 means the person came to work every day), it returns the number of absences for the week, that way we can see each worker's absenteeism by week. If there is no data (if the worker didn't come any day) a "n/a" (not applicable) appears. The formula for B2 in the summary table for example is:

=IF(COUNTIFS(Data!$A:$A,Summary!B$1,Data!$B:$B,Summary!$A2)=0,"n/a",ADDIFS(Data!$C:$C,Data!$A:$A,Summary!B$1,Data!$B:$B,Summary!$A2))

I am having difficulty sorting in the summary table by number of absences, so we can see who failed to show up the most and design policies in order to lower absenteeism. You can see in the attached picture, the column for week 202052 is arranged in order, yet they do not appear in order from "n/a" for example to 6-5-4-3-2-1 and finally 0.

Also, I would prefer for "n/a" to appear under "0", but this is secondary.

All cells in the summary are formatted for "numbers".

I have attached pictures of both tables.

Thanks!

 

[Fluff]

Remove all references to the summary sheet, you should never use the name of the sheet the formula is in.
Also what is ADDIFS? There is no such function in Xl.

 

[isafloor]

Remove all references to the summary sheet, you should never use the name of the sheet the formula is in.
Also what is ADDIFS? There is no such function in Xl.

Sorry I meant SUMIFS, I translated incorrectly from Spanish.

That solution worked perfectly, would there be a way to have "n/a" appear bellow "0" instead of above the largest numbers? I tried putting other symbols but they all appear before the largest numbers.

 

[Fluff]

Because a string is considered to be larger than a number, there is no simple way (that I know of) to sort them to the bottom.
One workaround would be to create a helper column where n/a becomes 0 & all numbers are increased by one. You could then sort on the helper.

 

[isafloor]

I looked at the pop up menu when you click the filter, and there is an option to personalize the order, that order is saved so that you can choose it in "order by color" and then "personalize order" and you clic on the saved option there. Not the ideal, but I guess it's better than nothing.

Cheers,

 

[Fluff]

Glad you found a solution & thanks for the feedback.