Forum Discussion
Data Aggregation base on dates
Hi there,
I want to aggregate purchase quantity base by date and place the result on a different cell. Can someone please shed some light on this. I am attaching the data table and output table here.
A product was purchased on the same date . There can be multiple purchased of the same product on the same date in different records. I want to aggregate it and display. Then the next purchased day should be considered , aggregate and put it as "2nd purchase" date. I can think of SUMIF. What is the best formula to do it. I thought of SUMIF. But I cann't figure out how to take the firstdate , 2nd date and 3rd date and aggregate it.
File attached , explaining input data and output data.
Thanks in advance!
12 Replies
- Thanks a lot! This really helps. I haven't tried yet. I hope it'll work with a given array as well as the table feature.
You didn't mention what version of Excel you use. I only develop solutions for Excel 365 which is a very different application from its predecessors (though it is backwards compatible, so I could develop old style formulas if I ever thought that was a good idea!).
= LET( date, UNIQUE(FILTER(Purchse_date, Product=@distinctProduct#)), qty, SUMIFS(Quantiy, Product, @distinctProduct#, Purchse_date, date), n, MIN(ROWS(date), 3), k, SEQUENCE(1,2*n,0), idx, 1+QUOTIENT(k, 2), choice, MOD(k,2), IF(choice, INDEX(qty,idx), INDEX(date,idx)) )The local name 'date' is a list of dates associated with a single product. 'qty' is the aggregated quantity associated with the product for a given date. The rest of the formula is simply rearranging these arrays into an alternating pattern as required.
- Thanks ! Yes I'm using 365. 🙂 This formula looks very sophisticated.
I'm bit confused how to use this though.This should at least demonstrate that the formula does work, even though how it works may remain a mystery. For me the good news is that Excel 365 is a very different app that enables completely different solution strategies. For everyone else, the good news may be that it is backward compatible, so you can keep on churning out the old familiar stuff!
For the time being try solution I've used in the attached file & let me try something better.
=====================================================
This is the best way I found & it's working.
How it works:
- For better data management & visualization I've altered the format of the OUTPUT, you can also realize that using formula on the format used by you is hectic as well precarious.
- An array (CSE) Formula in cell F26:
{=IFERROR(INDEX($B$26:$B$42, MATCH(SMALL(IF(COUNTIF($F$25:F25, $B$26:$B$42)=0, COUNTIF($B$26:$B$42, "<"&$B$26:$B$42), ""), 1), COUNTIF($B$26:$B$42, "<"&$B$26:$B$42), 0)),"")}- Use this formula in G26:
=SUMPRODUCT(($A$26:$A$42=G$25)*($B$26:$B$42=$F26)*($C$26:$C$42))- Cell G33 has SUM formula.
- Adjust cell references in the formula as needed.
- Finish an array (CSE) formula with Ctrl+Shift+Enter.
- Thanks! But I actually need the layout I mentioned in my file. I think I can use your formulas as well.
- It's okay,,, but the method used by Lewin is exactly what I've suggested through Test data file,, and the biggest issue was for quantity formula has to use for every column separately, which is not a good practice that's the reason I've suggested another one !!
ImalkaJ are you familiar with pivot? The table has a different look, but in case you do not have too many product types, it might do. I attached the possible solution, plus I made the input table a Table formatted area. Add new row to input --> Table expands automatically --> right click to pivot --> refresh
- Thanks ! For this I am opting out pivot table. I am more after advanced formula/ learn if I have to use VBA for this.
