This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
| Date | Description | cash credits | online credits | cash debits | online debits | Trans category | Month | 5 largest values | 5 smallest values |
|---|---|---|---|---|---|---|---|---|---|
| MAR-2023 | ? | ? | |||||||
My data in table format Table2. column A dates in ddmmmyy format starts from 01/jan/2023 to till date. Every day transactions entered in respective columns as per Transaction category codes, 4 letter codes. Few transaction codes are "FOOD", "TRAN", "SHOP","****", "TRNA". If I receive interest from my bank online the entry in my Table with date in "online credits" column amount I received and respective Trans Category entry will be "****" which denotes Bank Interest. All my expenses are entered datewise in "cash debits" and "online debits" columns. I have special category named as "TRNA" denotes Transaction not accountable, while paying through UPI or GPAY sometimes online debit occurs immediately and within some time transaction cancels and the debited amount credits to my account.
I need 5 largest/smallest expenses in a given month(H1) (both cash debits & online debits) excluding "TRNA" transactions. Entries in all columns are not mandatory on any date. Expenses on any day may be entered in any Debit columns as per my payment method, sometimes in Cash mode and/or online mode.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
Hi,
In cell H5, the formula should be
=CHOOSECOLS(TAKE(SORT(LET(ftr,FILTER(A2:F11,((F2:F11<>"TRNA")*(F2:F11<>"CAWD")*(TEXT(A2:A11,"mm-yy")=TEXT(H1,"mm-yy")))),HSTACK(CHOOSECOLS(ftr,1,2),BYROW(ftr,LAMBDA(a,SUM(CHOOSECOLS(a,4,5)))))),3,-1),3),3,1,2)
Hope this helps.
Answer accepted by question author
Hi,
In cell H5, enter this formula
=TAKE(SORT(LET(ftr,FILTER(A2:F11,((F2:F11<>"TRNA")*(F2:F11<>"CAWD")*(TEXT(A2:A11,"mm-yy")=TEXT(H1,"mm-yy")))),HSTACK(CHOOSECOLS(ftr,1,2),BYROW(ftr,LAMBDA(a,SUM(CHOOSECOLS(a,4,5)))))),3,-1),3)
In cell H11, enter this formula
=TAKE(SORT(LET(ftr,FILTER(A2:F11,((F2:F11<>"TRNA")*(F2:F11<>"CAWD")*(TEXT(A2:A11,"mm-yy")=TEXT(H1,"mm-yy")))),HSTACK(CHOOSECOLS(ftr,1,2),BYROW(ftr,LAMBDA(a,SUM(CHOOSECOLS(a,4,5)))))),3,1),3)
Hope this helps.
Answer accepted by question author
If you have Microsoft 365:
=LET(f, FILTER(E2:F1000, (G2:G100<>"TRNA")*(A2:A1000-DAY(A2:A1000)=H2-DAY(H2))), c, TOCOL(f), s, SORT(c, , -1), TAKE(s, 5))
and
=LET(f, FILTER(E2:F1000, (G2:G1000<>"TRNA")*(A2:A1000-DAY(A2:A1000)=H2-DAY(H2))), c, TOCOL(f), s, SORT(FILTER(c, c<>0)), TAKE(s, 5))
If you have Microsoft 365:
=LET(f, FILTER(E2:F1000, (G2:G100<>"TRNA")*(A2:A1000-DAY(A2:A1000)=H2-DAY(H2))), c, TOCOL(f), s, SORT(c, , -1), TAKE(s, 5))
and
=LET(f, FILTER(E2:F1000, (G2:G1000<>"TRNA")*(A2:A1000-DAY(A2:A1000)=H2-DAY(H2))), c, TOCOL(f), s, SORT(FILTER(c, c<>0)), TAKE(s, 5))
Thank You. Got correct results. What else added to this formula to get these 5 top/bottom values With Transaction date and Description in next two columns.
1 st top value --- Date--- Description
2 nd top value --- Date-- description
so on....
Hi Tkrajutkraju,
Thanks for contacting us,
To achieve this task in Excel, you can use a combination of formulas like SUMIFS, INDEX, MATCH, and LARGE for finding the top 5 largest and smallest expenses for a given month. Here's a step-by-step guide:
Assuming your data starts from cell A1 and your headers are in row 1, and the month you want to analyze is in cell H1, you can follow these steps:
In cell I2, enter the following formula to calculate the total expenses for "CASH DEBITS" excluding "TRNA":
=SUMIFS(C:C, A:A, ">="&DATEVALUE("01-"&H1&"-2023"), A:A, "<="&EOMONTH(DATEVALUE("01-"&H1&"-2023"),0), G:G, "<>TRNA")
In cell J2, enter the following formula to calculate the total expenses for "ONLINE DEBITS" excluding "TRNA":
=SUMIFS(E:E, A:A, ">="&DATEVALUE("01-"&H1&"-2023"), A:A, "<="&EOMONTH(DATEVALUE("01-"&H1&"-2023"),0), G:G, "<>TRNA")
In cell K2, enter the following formula to find the top 5 largest expenses for "CASH DEBITS":
=INDEX(C:C, MATCH(LARGE(IF((A:A>=DATEVALUE("01-"&H1&"-2023"))\*(A:A<=EOMONTH(DATEVALUE("01-"&H1&"-2023"),0))\*(G:G<>"TRNA"), C:C), ROW(INDIRECT("1:5"))), IF((A:A>=DATEVALUE("01-"&H1&"-2023"))\*(A:A<=EOMONTH(DATEVALUE("01-"&H1&"-2023"))\*(G:G<>"TRNA")), C:C), 0))
In cell L2, enter the following formula to find the top 5 largest expenses for "ONLINE DEBITS":
=INDEX(E:E, MATCH(LARGE(IF((A:A>=DATEVALUE("01-"&H1&"-2023"))\*(A:A<=EOMONTH(DATEVALUE("01-"&H1&"-2023"))\*(G:G<>"TRNA")), E:E), ROW(INDIRECT("1:5"))), IF((A:A>=DATEVALUE("01-"&H1&"-2023"))\*(A:A<=EOMONTH(DATEVALUE("01-"&H1&"-2023"))\*(G:G<>"TRNA")), E:E), 0))
In cell M2, enter the following formula to find the top 5 smallest expenses for "CASH DEBITS":
=INDEX(C:C, MATCH(SMALL(IF((A:A>=DATEVALUE("01-"&H1&"-2023"))\*(A:A<=EOMONTH(DATEVALUE("01-"&H1&"-2023"))\*(G:G<>"TRNA")), C:C), ROW(INDIRECT("1:5"))), IF((A:A>=DATEVALUE("01-"&H1&"-2023"))\*(A:A<=EOMONTH(DATEVALUE("01-"&H1&"-2023"))\*(G:G<>"TRNA")), C:C), 0))
In cell N2, enter the following formula to find the top 5 smallest expenses for "ONLINE DEBITS":
=INDEX(E:E, MATCH(SMALL(IF((A:A>=DATEVALUE("01-"&H1&"-2023"))\*(A:A<=EOMONTH(DATEVALUE("01-"&H1&"-2023"))\*(G:G<>"TRNA")), E:E), ROW(INDIRECT("1:5"))), IF((A:A>=DATEVALUE("01-"&H1&"-2023"))\*(A:A<=EOMONTH(DATEVALUE("01-"&H1&"-2023"))\*(G:G<>"TRNA")), E:E), 0))
Make sure to enter these formulas in the respective cells and adjust cell references as needed based on your data layout.
Let me know if this helps or if you need further clarifications.
Regards, Sola
“Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below. “
Kindly note that this is a user to user forum, we are users helping other users, we aren't Microsoft employee neither are we Microsoft agents.