+ Reply to Thread
Results 1 to 11 of 11

countif current month excel 2007

  1. #1
    Registered User
    Join Date
    04-08-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    8

    countif current month excel 2007

    I am attempting to track sales occurrences per day, week, month, year.

    The day is working using the following:
    =COUNTIFS(E8:E100014, TODAY())

    Also working is the current week:
    =(SUMPRODUCT((E8:E10014+0>=TODAY()-WEEKDAY(TODAY())+1)*(E8:E10014+0<=TODAY()-WEEKDAY(TODAY())+7)))

    I am failing terribly when attempting to simply count the number of occurences within column E.

    I feel as my job has turned into spreadsheets. Possibly worth pointing out is the worksheet I use is a destination of multiple source worksheets.

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

    Re: countif current month excel 2007

    Do you mean: count the unique value (days) in E column?
    If yes, try:
    =SUMPRODUCT(1/COUNTIF(E8:E100014,E8:E100014))
    If not, try to go into more details what you need beside the two your formulas.

  3. #3
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: countif current month excel 2007

    H acecalder

    I am attempting to track sales occurrences per day, week, month, year
    Use the sumproduct ie =SUMPRODUCT(--(MONTH(E1:E35)=6)) where 6=June. Etc
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: countif current month excel 2007

    For current month try

    =COUNTIFS(E8:E10014,">="&EOMONTH(TODAY(),-1)+1,E8:E10014,"<"&EOMONTH(TODAY(),0)+1)
    Audere est facere

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: countif current month excel 2007

    Quote Originally Posted by acecalder View Post
    I am attempting to track sales occurrences per day, week, month, year......
    It might be simpler to have cells that would give you start and end of the current week, month year, e.g.

    for start of month

    =EOMONTH(TODAY(),-1)+1

    for start of year

    =DATE(YEAR(TODAY()),1,1)

    etc.

    and then you can use a single formula syntax for each period, e.g. with start of period in G2 and end of period in H2 just use

    =COUNTIFS(Dates,">="&G2,Dates,"<="&H2)

  6. #6
    Registered User
    Join Date
    04-08-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: countif current month excel 2007

    =COUNTIFS(E8:E10014,">="&EOMONTH(TODAY(),-1)+1,E8:E10014,"<"&EOMONTH(TODAY(),0)+1)

    Works like a charm! I very much appreciate the assistance from all.

  7. #7
    Registered User
    Join Date
    04-08-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: countif current month excel 2007

    As a follow up I wonder if it is possible to count financial quarters. Same everything but the output would be count if date Q1 etc

  8. #8
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: countif current month excel 2007

    How do you define the quarters, is it April 1 to June 30, Jul 1 to Sept 30 etc.? If so then for current quarter try this

    =COUNTIFS(E8:E10014,">="&EOMONTH(TODAY(),MOD(-MONTH(TODAY()),3)-3)+1,E8:E10014,"<"&EOMONTH(TODAY(),MOD(-MONTH(TODAY()),3))+1)

  9. #9
    Registered User
    Join Date
    04-08-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: countif current month excel 2007

    This formula seems to work for the quarter. Wonderful. Karma is the only thing I could offer as payment.

  10. #10
    Registered User
    Join Date
    04-08-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: countif current month excel 2007

    The week total has stopped working this morning for some reason and I have no idea why. Is there something wrong with this?

    =(SUMPRODUCT((E8:E10015+0>=TODAY()-WEEKDAY(TODAY())+1)*(E8:E10015+0<=TODAY()-WEEKDAY(TODAY())+7)))

  11. #11
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: countif current month excel 2007

    Why are you adding Range+0 ?

    Why don't use cells to hold the date boundaries:

    A2: =TODAY()-WEEKDAY(TODAY())+1
    B2: =A2+6

    Then, you can use this formula:

    =COUNTIFS(E8:E10015,">="&A2,E8:E10015,"<="&B2)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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