SOLVED

# Need simplified version

Occasional Contributor

# Need simplified version

Hi all,

I tried to work this out myself, but it was very labor intensive and not very tidy. For some reason, the formulas wouldn't spill!

i have a list of names, that appear in several arrays on one spreadsheet. these names have results in the same row that i need.

I want to collect these results and tally them next to the same name on a new spreadsheet.

On top of that the names need to ordered according the results tally from highest to lowest.

i have done all this except for the ordering from highest to lowest in the screenshots below

i actually only need to show the info in columns A and B.

Hope i have explained myself clearly enough.

Cheers Richard.

7 Replies

# Re: Need simplified version

Hello @Tinny426

OK why not use the SUMIF() function?

Look it up and see if it will work for you. It is backward compatible to MS-Excel ??? Gosh how old is that???

Let us know.

Georgie Anne

# Re: Need simplified version

Attach a sample workbook. You may need SORT() function.

# Re: Need simplified version

@Harun24HR Hello Harun,

See attached workbook that i am working on.

Thanks Richard.

# Re: Need simplified version

Hello GeorgieAnne,
I have used the SUMIF() function along with my other functions and it works, but its long!
SUM(XLOOKUP(A3,'Competition results'!E3:E100,'Competition results'!I3:I100),XLOOKUP(A3,'Competition results'!U3:U100,'Competition results'!X3:X100)).
And its only going to get longer when i add more XLOOKUP() FUNCTIONS.
I was hoping for just the one function that i can SUM().
Cheers Richard.

# Re: Need simplified version

Hi Harun, have you had a chance to look at my worksheet yet?
best response confirmed by Tinny426 (Occasional Contributor)
Solution

# Re: Need simplified version

Can you check whether the following formula in cell B3 brings the desired result? If not, I may have misunderstood something.

``=INDEX(XLOOKUP(A3:A22,'Competition results'!K3:K100,'Competition results'!O3:O100),ROW(1:20))+INDEX(XLOOKUP(A3:A22,'Competition results'!V1:V98,'Competition results'!Y1:Y98),ROW(1:20))``

Excel Insider or Excel for the Web can also use the following formula:

``=BYROW(HSTACK(XLOOKUP(A3:A22,'Competition results'!K3:K100,'Competition results'!O3:O100),XLOOKUP(A3:A22,'Competition results'!V1:V98,'Competition results'!Y1:Y98)),LAMBDA(in,SUM(in)))``