Filtering Dates within the dates and adding corresponding values

SanjayGMusafir

SanjayGMusafir

Well-known Member
Joined
Sep 7, 2018
Messages
1,470
Office Version
  1. 2021
Platform
  1. MacOS
Hi Experts

Got stuck up in a strange situation. Tried copying XL2BB from original file - It failed. So copied the actual data to a blank worksheet to explain a situation.

What I need to do -
  1. Filter dates based on Financial year (April 1 to March 31 - in India) - May see the blue highlighted rows
  2. Add the values in column B out of that filtered data BUT
    1. Take the Value corresponding to date which is Higher B12 (Highlighted Red) for same values in Column C
    2. Include B12 Value and exclude B10 value as A10 is smaller than A12
Tried different ways. But unable to bring it (all) in one formula as I'm making Year over Year, Year End summary.

Please help

Thanks in advance.

Book1
ABCD
112/31/2134,400405 FF
212/31/2114,000979/GH5&7
34/30/2211,000527/GH5&7
46/30/2213,500647/GH5&7
511/30/2213,0001197/GH5&7
611/30/2210,000256/GH5&7
711/30/2238,200405 FF
811/30/2215,000979/GH5&7
93/31/2312,000527/GH5&7
105/31/2314,580647/GH5&7
117/31/2310,000720/GH5&7
128/30/2315,700647/GH5&7
139/29/2312,800527/GH5&7
1410/31/2314,0001197/GH5&7
1510/31/2310,000256/GH5&7
1610/31/2341,300405 FF
1710/31/2316,500979/GH5&7
188/31/2411,000720/GH5&7
199/30/2415,4001197/GH5&7
209/30/2410,500256/GH5&7
219/30/2444,600405 FF
229/30/2416,500979/GH5&7
232/28/2512,500647/GH5&7
243/31/2512,000527/GH5&7
Sheet1
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Not sure about this one:
MrExcelPlayground22.xlsx
ABCDEF
112/31/202134400405 FFFY
212/31/202114000979/GH5&7202148400
34/30/202211000527/GH5&72022101700
46/30/202213500647/GH5&72023120300
511/30/2022130001197/GH5&72024122500
611/30/202210000256/GH5&7
711/30/202238200405 FF
811/30/202215000979/GH5&7
93/31/202312000527/GH5&7
105/31/202314580647/GH5&7
117/31/202310000720/GH5&7
128/30/202315700647/GH5&7
139/29/202312800527/GH5&7
1410/31/2023140001197/GH5&7
1510/31/202310000256/GH5&7
1610/31/202341300405 FF
1710/31/202316500979/GH5&7
188/31/202411000720/GH5&7
199/30/2024154001197/GH5&7
209/30/202410500256/GH5&7
219/30/202444600405 FF
229/30/202416500979/GH5&7
232/28/202512500647/GH5&7
243/31/202512000527/GH5&7
Sheet18
Cell Formulas
RangeFormula
E2:E5E2=UNIQUE(YEAR(EOMONTH(+A1:A24,-3)))
F2:F5F2=LET(a,FILTER($A$1:$C$24,YEAR(EOMONTH(+$A$1:$A$24,-3))=E2),b,UNIQUE(CHOOSECOLS(a,3)),c,SORT(a,1),d,XLOOKUP(b,CHOOSECOLS(c,3),CHOOSECOLS(c,2),"",0,-1),SUM(d))
Dynamic array formulas.
 
Upvote 0
Thanks @JamesCanale This is the exact result I want. Only challenge is ChooseCols function is not available in my Excel 2021 (Mac)

Is there a way to bypass that?

Thanks again
 
Upvote 0
I'm not savvy on 2021 - try this:
MrExcelPlayground22.xlsx
ABCDEFG
112/31/202134400405 FFFY
212/31/202114000979/GH5&720214840048400
34/30/202211000527/GH5&72022101700101700
46/30/202213500647/GH5&72023120300120300
511/30/2022130001197/GH5&72024122500122500
611/30/202210000256/GH5&7
711/30/202238200405 FF
811/30/202215000979/GH5&7
93/31/202312000527/GH5&7
105/31/202314580647/GH5&7
117/31/202310000720/GH5&7
128/30/202315700647/GH5&7
139/29/202312800527/GH5&7
1410/31/2023140001197/GH5&7
1510/31/202310000256/GH5&7
1610/31/202341300405 FF
1710/31/202316500979/GH5&7
188/31/202411000720/GH5&7
199/30/2024154001197/GH5&7
209/30/202410500256/GH5&7
219/30/202444600405 FF
229/30/202416500979/GH5&7
232/28/202512500647/GH5&7
243/31/202512000527/GH5&7
Sheet18
Cell Formulas
RangeFormula
E2:E5E2=UNIQUE(YEAR(EOMONTH(+A1:A24,-3)))
F2:F5F2=LET(a,FILTER($A$1:$C$24,YEAR(EOMONTH(+$A$1:$A$24,-3))=E2),b,UNIQUE(CHOOSECOLS(a,3)),c,SORT(a,1),d,XLOOKUP(b,CHOOSECOLS(c,3),CHOOSECOLS(c,2),"",0,-1),SUM(d))
G2:G5G2=LET(a,FILTER($A$1:$C$24,YEAR(EOMONTH(+$A$1:$A$24,-3))=E2),b,UNIQUE(INDEX(a,,3)),c,SORT(a,1),d,XLOOKUP(b,INDEX(c,,3),INDEX(c,,2),"",0,-1),SUM(d))
Dynamic array formulas.
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

J
  • Question
VBA to sum values in one column based on criteria of another column
2
Replies
12
Views
8K
Jim885
J

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top