Results 1 to 4 of 4

Thread: SUMIF COUNTIF AVERAGE problem formula...
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2009
    Posts
    179
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default SUMIF COUNTIF AVERAGE problem formula...

    Hi all, hoping that somebody can help with this formula please...

    I have a list of contractors, all with multiple lines for queries (for different pieces of work), with the number of days they take to respond to each query and the fiscal year that the query falls into.

    So I have the following columns:

    L = Contractor name
    S = Number of days to respond
    AD = Fiscal year (i.e. 2018/2019).

    I then have a drop down elsewhere in the sheet with 'All Years' or the fiscal year (so it can be changed) and a summary table with each contractor in (listed in column B down).

    The formula I have come up with is:
    =IF($B$2="All Years",
    SUMIF($L$21:$L$504,B11,$S$21:$S$504)/COUNTIF($L$21:$L$504,B11),
    SUMIFS($L$21:$L$504,B11,$S$21:$S$504,$AD$20:$AD$504,$B$2)
    /COUNTIFS($L$21:$L$504,$B$10,$AD$21:$AD$504,$B$2))

    However it is throwing out a #Value ! error and I can't see why that would be? In the above example, B11 contains 'ABC'.

    I am trying to say, if B2 = All Years, then look at all of the contractors listed in column L, tell me the number of times 'ABC' appears and the number of cumulative days to respond, then divide that by the number of times 'ABC' appears in the column (to get the average).

    HOWEVER, if B2 = anything other than 'All Years' (i.e. a specific fiscal year period, like 2018/2019), then do the same calculation but factor in the number of instances the contractor appears in that financial year alone.

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,814
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: SUMIF COUNTIF AVERAGE problem formula...

    One thing that stands out is is looks like your SUMIFS is not structured correctly:
    Code:
    SUMIFS($L$21:$L$504,B11,$S$21:$S$504,$AD$20:$AD$504,$B$2)
    I think your second argument should be a whole range, not a single cell.
    See here for the proper structure: https://exceljet.net/excel-functions...umifs-function
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Sep 2009
    Posts
    179
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF COUNTIF AVERAGE problem formula...

    Great, thanks! Works!!

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,814
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: SUMIF COUNTIF AVERAGE problem formula...

    You are welcome.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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