+ Reply to Thread
Results 1 to 8 of 8

Using COUNTIF to aggregate daily data to weekly

Hybrid View

  1. #1
    Registered User
    Join Date
    10-06-2011
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    78

    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.
    Attached Files Attached Files
    Last edited by perducci; 01-19-2012 at 11:16 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Using COUNTIF to aggregate daily data to weekly

    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.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    10-06-2011
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Using COUNTIF to aggregate daily data to weekly

    That is the easiest but I was hoping to find a way without adding an extra column to the first table. I want another person to be able to cut and paste data into the table 1 and have it update the weekly data. Are there any other options?
    thanks

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Using COUNTIF to aggregate daily data to weekly

    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]]))

  5. #5
    Registered User
    Join Date
    10-06-2011
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Using COUNTIF to aggregate daily data to weekly

    That works. Thank you so much!

  6. #6
    Registered User
    Join Date
    10-06-2011
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Using COUNTIF to aggregate daily data to weekly

    I am baffled by this equation. I'm now trying to perform the same task but aggrthe data into months. Can someone explain the solution above and help me aggregate the count into months?

    Thanks

  7. #7
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    365
    Posts
    2,243

    Re: Using COUNTIF to aggregate daily data to weekly

    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))
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,651

    Re: Using COUNTIF to aggregate daily data to weekly

    Another option:
    H3:
    =SUMPRODUCT(1*(MONTH(Table1[Date])=Table2[[#This Row],[Month]]))
    I3:
    =SUMPRODUCT((MONTH(Table1[Date])=Table2[[#This Row],[Month]])*Table1[Price])
    Quang PT

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1