Results 1 to 3 of 3

Thread: SumProduct ???

  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: SumProduct ???

    Hi there,

    I have the following data entered on an excel spreadsheet.

    Column A Column B
    Row 1 1 6
    Row 2 1 150
    Row 3 2 150
    Row 4 3 0
    Row 5 4 1100
    Row 6 4 150
    Row 7 5 0
    Row 8 5 390
    Row 9 5 1100
    Row 10 6 0
    Row 11 6 0
    Row 12 6 150
    Row 13 7 0
    Row 14 7 0
    Row 15 7 150
    Row 16 8 0
    Row 17 9 0
    Row 18 11 150
    Row 19 12 150
    Row 20 13 390
    Row 21 14 0
    Row 22 15 0
    Row 23 25 390
    Row 24 Ass 0
    Row 25 Dec 0
    Row 26 Did 0
    Row 27 Did 0
    Row 28 Ear 0
    Row 29 Exi 0
    Row 30 Ful 0
    Row 31 Men 0
    Row 32 Men 0
    Row 33 P/T 0
    Row 34 Pre 0
    Row 35 REF 310
    Row 36 REF 310
    Row 37 REF 150
    Row 38 REF 150
    Row 39 Ref 0
    Row 40 Rel 0
    Row 41 Tra 0
    Row 42
    Row 43
    Row 44
    Row 45
    Row 46


    I have two columns.

    Column A has blank cells, alpha words, and numerics
    Column B has blank cells and numerics in each cell

    I want to count the number of cells in Column B which has 390 entered into it as long as column A has a numeric in the cell.
    ie if there is alpha text or a blank cell in column A, then I do not want the equivalent cell (same position in the array) of Column B to be counted
    The following is near but not close enough
    =SUMPRODUCT((B2:B47>=1)*(C2:C47>=0)) gives the answer of 46, which includes blank cells and alpha cells


    Thankyou in advance for any assistance given.

    Regards

    Karen Day
    Last edited by Karen Day; 07-03-08 at 05:07. Reason: not clearly showing on forum

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

    From your description I think this might do it

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

    ??

    If not, then I may have misinterpreted you problem!?


    MTB

  3. #3
    Join Date
    May 2002
    Posts
    157

    Thank you

    It works perfectly - thank you so much
    Regards
    Karen

Posting Permissions

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