# Thread: Formula Assistance Using SumProduct (03)

1. ## 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. ## 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. ## 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. ## Re: Formula Assistance Using SumProduct (03)

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

5. ## 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. ## Re: Formula Assistance Using SumProduct (03)

That's simple:

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

7. ## 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
•