Maybe if I use the frequency function?

[ExcelMBA]

I have a data table that registers daily sales and there are several sales per day. I need to create an array function that groups sales into single days. </SPAN></SPAN>
E.g.</SPAN></SPAN>
Date Product Sales</SPAN></SPAN>
1/1/13 X 50</SPAN></SPAN>
1/1/13 Y 100</SPAN></SPAN>
1/2/13 X 10</SPAN></SPAN>
1/2/13 X 20</SPAN></SPAN>
1/3/13 Y 50</SPAN></SPAN>
1/3/13 Z 200</SPAN></SPAN>
...

Result array must show:</SPAN></SPAN>

Date Sales</SPAN></SPAN>
1/1/13 150</SPAN></SPAN>
1/2/13 30</SPAN></SPAN>
1/3/13 250</SPAN></SPAN>
...

I could manage if this is achieved: create an array formula that changes this:</SPAN></SPAN>
</SPAN></SPAN>
{50,100,10,20,50,200...}</SPAN> </SPAN>

And summarizes data to this:</SPAN></SPAN>

{150,30,250...}</SPAN> </SPAN>

Hope someone can help. Thank you </SPAN></SPAN>

 

[vlady]

try sunif
=sumif(daterange,targetdate,salesrange)

 

[ExcelMBA]

Hi, I really appreciate your response but, remember that this data table is continually expanding and I need a more dynamic approach: a formula that creates an array with results grouped together in singe days. </SPAN>

try sunif
=sumif(daterange,targetdate,salesrange)

 

[cyrilbrd]

addition to Vlady's solution:
given in A1:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl64 { text-align: center; }.xl65 { text-align: center; }</style>

date	Product	Sales		date	sumif	sumproduct
1/1/13	X	50		1/1/13	150	150
1/1/13	Y	100		1/2/13	30	30
1/2/13	X	10		1/3/13	250	250
1/2/13	X	20
1/3/13	Y	50
1/3/13	Z	200

<tbody>
</tbody>

Formula in F2 is =SUMIF($A$2:$A$7,E2,$C$2:$C$7), this would be faster than Sumproduct
Formula in G2 is =SUMPRODUCT(--($A$2:$A$7=E2),$C$2:$C$7), now although a tad slower, Sumproduct accepts arrays where Sumif/s doesn't. This is useful when using Sumproduct for advanced queries.

 

[cyrilbrd]

Hi, I really appreciate your response but, remember that this data table is continually expanding and I need a more dynamic approach: a formula that creates an array with results grouped together in singe days.

You could use a dynamic Named range such as 'date'=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) for the date and 'sales'=OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$C),1) for the sales.
your formula in F2 would become =SUMIF(date,E2,sales)

 

[ExcelMBA]

Thanks Cybrid!
Although I think I should better explain my situation.
I have a data table like I explained before, lots of entries but most of the time there are repeated dates. Now, I want to calculate a standard deviation of daily sales for a specific product, that´s why I need to create a range that groups sales into each day.
I know I can easily do this with the group option in pivot tables, but I need to create a formula to run directly into the spreadsheet.
Hope I explained myself better this time :S

Thanks again for your help

You could use a dynamic Named range such as 'date'=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) for the date and 'sales'=OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$C),1) for the sales.
your formula in F2 would become =SUMIF(date,E2,sales)

 

[cyrilbrd]

for standard deviation you may use =STDEV((IF(date=E2,sales))) Ctrl + Shift + Enter, not just enter on a PC or Command + Return on a MAC.
please take note that the above mentioned uses the named ranges as described in post#5
would that work?

 

[ExcelMBA]

That would work for a fixed set, but the data table I mentioned is constantly growing so there would be new dates to group.

 

[cyrilbrd]

That would work for a fixed set, but the data table I mentioned is constantly growing so there would be new dates to group.

sorry for asking but have you tried the solution proposed for this specific concern? that is two named ranges.
the named ranges would 'grow' with your data... may it start with only five rows and end up with 20000 rows, the formula would consider the whole range and return the appropriate calculation. or am I missing something?

 

[cyrilbrd]

That would work for a fixed set, but the data table I mentioned is constantly growing so there would be new dates to group.

given in A1:

date	Product	Sales		4
1/1/13	X	50		Dates	sum	stdv
1/1/13	Y	100		1/1/13	210	26.458
1/2/13	X	10		1/2/13	30	7.071
1/2/13	X	20		1/3/13	250	106.066
1/3/13	Y	50		1/4/13	84	11.314
1/3/13	Z	200			0
1/1/13	x	60			0
1/4/13	z	34			0
1/4/13	z	50

<tbody>
</tbody>

Not sure if this is what you want, but want to create a unique list from your column A.
Insert / Name / Define: create "Rvec" with the following formula: =ROW(Sheet1!$A$2:$A$11000)-ROW(Sheet1!$A$2)+1

Then in E1 type =SUM(IF(FREQUENCY(IF(date<>"",MATCH("~"&date,date&"",0)),Rvec),1)) Ctrl + Shift + Enter, not just enter on a PC or Command + Return on a MAC.

This will return the count of unique items found in Column A (till row 11000).
In E2 type =IF(ROWS($E$2:E2)<=$E$1,INDEX(date, SMALL(IF(FREQUENCY(IF(date<>"", MATCH("~"&date,date&"",0)),Rvec),Rvec), ROWS($E$2:E2))),"") Ctrl + Shift + Enter, not just enter on a PC or Command + Return on a MAC.
Copied down till needed
F3 is =IFERROR(SUMIF(date,E3,sales),"")
G3 is =IFERROR(STDEV((IF(date=E3,sales))),"")Ctrl + Shift + Enter, not just enter on a PC or Command + Return on a MAC.

This will return all distinct (unique) values found in Column "Item".

Is this what you needed, thus would be dynamic, would make your list of dates to group expandable with your raw data.