Results 1 to 7 of 7

20070207, 18:07 #1
 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

20070207, 18:08 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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?

20070207, 18:11 #3
 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

20070207, 18:11 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Formula Assistance Using SumProduct (03)
I'm afraid I don't understand what the purpose of the second formula is.

20070207, 18:14 #5
 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

20070207, 18:19 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Formula Assistance Using SumProduct (03)
That's simple:
=SUMPRODUCT((A23:A27>=57400)*(A23:A27<=57499),B23: B27)

20070207, 18:37 #7
 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.