Take,ChooseCols,Sort Function Not Showing Output In A Particular Cell

Good Morning Excel Guru?s,

I have finally been able to correct the formulas from the help of others here in the group (AliGW and CheeseSandwhich) with a filtering and sorting formulas.

I have come across an issue with the output whereby in the examples I have provided in the attachment using the TAKE function nested with Choose and sort have two different results for me.

In the 1st example, the total points is a simple sum of two cells. And when creating a filter of this table using the Take, Choose and Sort functions (nested), the output of total points appear.



However, in 2nd example, the total points is based on the IF,AND,OR,ISBLANK,NOT formula and using the same take, choose and sorted (nested) the output on Total Points appears blank.

How do I fix this for example 2 to show the total points using the IF,AND,OR,ISBLANK,NOT formula?

Book3.xlsx

a Null string ("") is > Number, the SORT is therefore putting the Null results first, hence no Numeric totals.

you can change your formula in H14 to use 0 rather than Null String, and you'll get your results; if you want to mask/hide the 0 consider applying a Custom Format to H14:H22, e.g. [=0]"";#

Hi XLent,
That does not quite work, the output is then showing zeros (see image)
Attachment 835682

You could use filter to remove the blank rows

Formula:

Please Login or Register  to view this content.

Hi HansDouwe,
I there is additional information if different columns in the original spreadsheet (do not want to share) and that needs to be triggered once it has met my criteria. This is why I am using these functions.

Hi Fluff13,
Unfortunately this does not work either.

Unfortunately this does not work either.

In what way?

Hi Fluff13,
See image. Your recommendation is in the highlighted in green. Attachment 835691

Hi HansDouwe,
That works for the original table, but as per my attachment (Book 3) the second table does not give the same output (total points) as per first table. I am hoping for solution on the second table and the output table to the right of that to be same. Does that make sense?

You're attachment is invalid, so I cannot see it.

Fluff13,
Not sure why, when I click on the link I a can see the image.

Book4.xlsx

An Updated worksheet named Book 4 has been uploaded.

I have attached an updated worksheet (Book 4).

What are your expected results?
Also you have now changed col H so that it has "0" in it which is text & not a number.

This works for me, but it's basically the same as the formula I originally posted so

Formula:

Please Login or Register  to view this content.