Thread: Using COUNTIF to aggregate daily data to weekly

I have data that shows an item description and the sale date, stored in a table. I want to count the number of sales made in a week using the countif function and store it in another table. I have looked and looked, and can't seem to find a solution. Please see the attached Excel file.

I wish I could use something like the following function, but it is invalid.
=COUNTIF(WEEKNUM(Table1[Date]),[@Week])

Thanks in advance.

The best/easiest way is to add another column to the main table with formula: =WEEKNUM(B3) copied down

and then:

=COUNTIF(Table1[Week Number],Table2[[#This Row],[Week]])

where Week Number is new column header.

Try then:

=SUMPRODUCT(--((1+INT((Table1[Date]-DATE(YEAR(Table1[Date]+4-WEEKDAY(Table1[Date]+6)),1,5)+WEEKDAY(DATE(YEAR(Table1[Date]+4-WEEKDAY(Table1[Date]+6)),1,3)))/7))=Table2[[#This Row],[Week]]))

In G3 to down enter first day of each month. eg: 1/1/2012, 2/1/2012 etc.. then

for the COUNT,

=COUNTIFS(Table1[Date],">="&G3,Table1[Date],"<="&EOMONTH(G3,0))

For the Revenue,

=SUMIFS(Table1[Price],Table1[Date],">="&G3,Table1[Date],"<="&EOMONTH(G3,0))

Another option:
H3:

=SUMPRODUCT(1*(MONTH(Table1[Date])=Table2[[#This Row],[Month]]))

I3:

=SUMPRODUCT((MONTH(Table1[Date])=Table2[[#This Row],[Month]])*Table1[Price])