Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula Assistance Using SumProduct (03)

    Can a range of cells be included in a SUMPRODUCT formula?

    I know that the following formula works if Cell A1 = 57400 and Cell A2 = 57499:

    =SUMPRODUCT((A23:A27>=A1)*(A23:A27<=A2),B23:B27)

    I'm thinking of something similar to: =SUMPRODUCT((A23:A27,">=57400")*(A23:A27,"<=57499" ),B23:B27)

    <table border=1><td></td><td>Column A</td><td>Column B</td><td>Row</td><td>Account</td><td>Amount</td><td>23</td><td>57399</td><td>10</td><td>24</td><td>57400</td><td>20</td><td>25</td><td>57450</td><td>30</td><td>26</td><td>57499</td><td>10</td><td>27</td><td>57500</td><td>50</td></table>


    Thanks,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula Assistance Using SumProduct (03)

    The formula

    =SUMPRODUCT((A23:A27>=A1)*(A23:A27>=A2),B23:B27)

    is valid but may not do what you intended. If the value is 57499 or more, it is automatically more than 57400. Did you mean <=A2 instead of >=A2 ?

    The formula

    =SUMPRODUCT((A23:A27,">=57400")*(A23:A27,">=57499" ),B23:B27)

    makes no sense - it uses elements from SUMIF or COUNTIF, but SUMPRODUCT doesn't work that way. What are you trying to accomplish?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Assistance Using SumProduct (03)

    Hans,

    I had a typo in both formulas. I corrected my original post.

    The returned value of the working formula is 60. Where the range is between 57400 and 57499.

    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula Assistance Using SumProduct (03)

    I'm afraid I don't understand what the purpose of the second formula is.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Assistance Using SumProduct (03)

    Hans,

    Is is a crude example of what I would like to express in the formula. The first formula works if Cell A1 and Cell A2 contain the lower and upper range. I would like not to reference Cell A1 and Cell A2 in the formual but substitute 57400 and 57499 into the equation.

    John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula Assistance Using SumProduct (03)

    That's simple:

    =SUMPRODUCT((A23:A27>=57400)*(A23:A27<=57499),B23: B27)

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Assistance Using SumProduct (03)

    Thank you it worked.

Posting Permissions

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