+ Reply to Thread
Results 1 to 4 of 4

COUNTIFS within date range & no 0 values.

  1. #1
    Registered User
    Join Date
    09-11-2016
    Location
    USA
    MS-Off Ver
    2010
    Posts
    2

    COUNTIFS within date range & no 0 values.

    Hi, i'd appreciate if someone can assist me in solving this #DIV/0! error, been stuck on it for quite some time.

    I am trying use cell D1 (9/7/2016) to get the sum of the prices since the beginning of the month & same year up to the date then divide by the number of days which don't have $0.00 values within that month.

    In the sample below, I should be dividing $157.06 by 5. However my avg keeps giving me the DIV error because its dividing 157.06 by 0.

    The formula for my avg is as follows: =SUMPRODUCT((B2:B14)*(MONTH(A2:A14)=MONTH(E1))*(YEAR(A2:A14)=YEAR(E1))*(DAY(A2:A14)<=DAY($E$1)))/(COUNTIFS(A2:A14,SUMPRODUCT(--(MONTH(A2:A14)=MONTH(E1))*(YEAR(A2:A14)=YEAR(E1))),B2:B14,">0"))

    Please Login or Register  to view this content.
    Thank you.
    Last edited by Joeway; 09-11-2016 at 04:33 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: COUNTIFS within date range & no 0 values.

    Welcome to the board.

    A
    B
    C
    D
    E
    1
    DATE
    Price
    9/7/2016
    2
    8/27/2016
    $0.00
    3
    8/28/2016
    $0.00
    SUM
    157.06
    D3: =SUMIFS($B$2:$B$14, $A$2:$A$14, ">=" & EOMONTH($D$1, -1) + 1, $A$2:$A$14, "<=" & $D$1)
    4
    8/29/2016
    $43.86
    AVG
    31.412
    D4: =AVERAGEIFS($B$2:$B$14, $A$2:$A$14, ">=" & EOMONTH($D$1, -1) + 1, $A$2:$A$14, "<=" & $D$1, $B$2:$B$14, "<>0")
    5
    8/30/2016
    $39.24
    6
    8/31/2016
    $38.59
    7
    9/1/2016
    $31.06
    8
    9/2/2016
    $26.79
    9
    9/3/2016
    $0.00
    10
    9/4/2016
    $0.00
    11
    9/5/2016
    $22.58
    12
    9/6/2016
    $35.70
    13
    9/7/2016
    $40.93
    14
    9/8/2016
    $44.53
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    09-11-2016
    Location
    USA
    MS-Off Ver
    2010
    Posts
    2

    Re: COUNTIFS within date range & no 0 values.

    , that worked perfectly. thank you very much

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: COUNTIFS within date range & no 0 values.

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Countifs match criteria and date is equals to or within date range
    By tiggi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-28-2016, 11:00 AM
  2. Adding corresponding values of a date range (SUMIFS & COUNTIFS)
    By Apol21 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-29-2016, 12:32 PM
  3. Replies: 2
    Last Post: 05-28-2014, 06:52 AM
  4. Countifs, If SUM of range is between two values.
    By SalientAnimal in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-28-2014, 09:34 AM
  5. using countifs to track unique values over a date range
    By msnyderxc in forum Excel General
    Replies: 1
    Last Post: 03-21-2014, 11:48 AM
  6. Using countifs with date range, and summing values in that range
    By bmcoonan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-30-2013, 11:28 AM
  7. COUNTIFS - Count all values in a range that equal any value in another range.
    By HeebieGeebie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-12-2013, 09:05 AM

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