SOLVED

Need simplified version

Copper Contributor

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.

Tinny426_0-1664059345806.jpeg

 

 

Tinny426_1-1664059346033.jpeg

 

 

7 Replies
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
Attach a sample workbook. You may need SORT() function.

@Harun24HR Hello Harun,

 

See attached workbook that i am working on.

 

Thanks Richard.

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.
Hi Harun, have you had a chance to look at my worksheet yet?
best response confirmed by Tinny426 (Copper Contributor)
Solution

@Tinny426 

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)))

 

Thanks for your reply dscheikey,
Your formula works for the job, but i came up with this formula, which works too.
=SUM(XLOOKUP(F6,'Competition results'!$K$3:$K$100,'Competition results'!$O$3:$O$100),XLOOKUP(F6,'Competition results'!$Z$3:$Z$100,'Competition results'!$AC$3:$AC$100).
i have made other improvements as well, so its easier to just stick with the formula i have.
1 best response

Accepted Solutions
best response confirmed by Tinny426 (Copper Contributor)
Solution

@Tinny426 

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)))

 

View solution in original post