Forum Discussion
Solved
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 he...
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 !!
