Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: average and sumproduct

    Hi there,

    I have two columns of data spanning 100 rows

    column B can be made up of numerics and alphabetics.
    column C is made up of numerics being either 150, 310, 390, or 1100.

    I want to count the number of cells in Column C which has 390 entered into it as long as column B has a numeric in the cell.

    In a previous Thread - MTB replied back suggesting the following formula which works perfectly:

    =SUMPRODUCT(ISNUMBER(B2:B47)*(C2:C47=390))

    I want to go a step further, in that I want to find the average of the total numerics in column B for those cells which have 150 entered into the corresponding cell in column C.

    So, for example....

    Column A Column B
    2 390
    11 150
    17 390
    6 1100
    9 150
    8 390
    20 150
    blank 150
    EXT 150
    Ext 390

    In this case I have only 3 figures in column A which have 150 entered in column B:
    Column A Column B
    11 150
    9 150
    20 150

    The sum of Column A (where there is a 150 in column B) is 40 and as only 3 figures make up this total then the average is 13.33



    I hope I have made myself clear. Please do not hesitate to contact me if you need any more detail.

    Thankyou in advance for any assistance given.

    Regards

    Karen Day

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi me again

    How about this

    =SUMIF(B2:B11,"=150",A2:A11)/SUMPRODUCT(ISNUMBER(A2:A11)*(B2:B11=150))

    ??

    MTB

  3. #3
    Join Date
    May 2002
    Posts
    157
    The formula works perfectly.

    Thank you so very much.

    Regards

    Karen Day

Posting Permissions

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