• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

formula rank + sumifs

yande

New Member
Greetings for all experts and master here.

I want to create some formula that combine 2 function, RANK and SUMIFS.
In my data table is a raw data contain multiple duplication of names but its not a duplication data, its actual record by date that make it looks duplicates.
Kindly refer my attachment.
 

Attachments

  • YANDE EXAMPLE.xlsx
    637.1 KB · Views: 4
A number of things come to mind examining your data. Firstly, the same (time consuming) calculations are performed many times because of the layout of the data. A pivot table might summarise the data better.
71473

Alternatively, can one assume the data is sorted? If so, one could search for the start and end of data associated with a specific client and only sum over the limited range.
71474

Code:
DebtorTransactions
= XLOOKUP(@Debtor#, Loans[Debtor], Loans[Sub Total],,,1) :
   XLOOKUP(@Debtor#, Loans[Debtor], Loans[Sub Total],,,-1)

Yearly Totals
= SUMIFS(DebtorTransactions, TransactionYear, Year)

Finally, how do you wish to rank the entries? Removing the duplicates helps but does not itself provide a solution.

p.s. I hope this is not real data.
 
I did some timing runs on my recalculation versus the original. Using Charles Williams's timing routines, a couple of test runs came out to 77.7 milliseconds on the first test and 80.6 ms on the second attempt. I only performed a single test on the original and returned 42540 milliseconds.

It is not often one gets a calculation done 500 times faster.
 
A number of things come to mind examining your data. Firstly, the same (time consuming) calculations are performed many times because of the layout of the data. A pivot table might summarise the data better.
View attachment 71473

Alternatively, can one assume the data is sorted? If so, one could search for the start and end of data associated with a specific client and only sum over the limited range.
View attachment 71474

Code:
DebtorTransactions
= XLOOKUP(@Debtor#, Loans[Debtor], Loans[Sub Total],,,1) :
   XLOOKUP(@Debtor#, Loans[Debtor], Loans[Sub Total],,,-1)

Yearly Totals
= SUMIFS(DebtorTransactions, TransactionYear, Year)

Finally, how do you wish to rank the entries? Removing the duplicates helps but does not itself provide a solution.

p.s. I hope this is not real data.
dont worry this not a real data, i want to create the formula inside the table for rank of subtotal.

example:

71477

The rank shows as the pic above, using pivot table much more easier to sums all the data, but i need the rank shows in the table like that.
I'd tried many times searching a formula but i failed.
is there a formula for this case or any other ways able to give a solution for this?
 
A formula that able to shows the ranking from the highest and the lowest, by year and by person.
 
This is the problem when i using pivot table with rank.
71479

The pivot table failed to sort large to smallest even tho already set in setting.
 

Attachments

  • YANDE EXAMPLE.xlsx
    741.7 KB · Views: 1
To improve the efficiency of the calculation, I separated the customer table from the transaction table.
If necessary, an XLOOKUP or VLOOKUP could bring the totals and the ranking data back to the transaction table.
 

Attachments

  • YANDE EXAMPLE.xlsx
    481.4 KB · Views: 2
yande
... how about those my given values (#7 reply) ?
Your is there a formula for this case or any other ways able to give a solution for this? (from #4 Reply)
The answer is 'Yes'.
 
It will be the XLOOKUP that is missing.
Code:
= INDEX(
    Loans[Sub Total],
    1+IFERROR(MATCH(@Debtor,
    Loans[Debtor]&"Z"),0))
  :
  INDEX(
    Loans[Sub Total],
    MATCH(@Debtor,
    Loans[Debtor]))
will return the range that starts with the first occurrence of the debtor name and ends with the last. Not the most appealing formula in town! :(
 

Attachments

  • YANDE EXAMPLE.xlsx
    481.6 KB · Views: 2
@vletm
#7 works fine; I wasn't sure whether I was ranking based upon 2019, 2020 or the grand total. Though, if you are going to use direct references to pick out data from a pivot table, why not do it twice?
= RANK(M$8:M$342, M$8:M$342)
 
Peter Bartholomew
I have asked from yande, are those values as expected.
Only yande would know - what really expected?
#5 Reply the ranking from the highest and the lowest, by year and by person.
#6 Reply yande shows something ... but not as written in #5
If those values would be as expected or something else,
then I would solve those 'my way' - without those formulas (#4 Reply ... or any other ways able to give a solution for this?).
( because, my sample values would work only with that data )
 
yande
As I've written ... "Yes" ... but I would like to know exact ... what? ... how?
Could You give sample result with Your sample data?
... okay ...
Here is my sample without Yours...
Press [ YANDE ]-button
and
You'll get Your results soon.
 

Attachments

  • YANDE EXAMPLE.xlsb
    234.6 KB · Views: 0
Last edited:
In the attached is a table to the right of yours - does this contain the correct values?
Right-click the new table and choose Refresh to update.
 

Attachments

  • Chandoo45132YANDE EXAMPLE.xlsb
    326.2 KB · Views: 2
Back
Top