# Thread: SUMIF using between (97; SR2)

1. ## SUMIF using between (97; SR2)

Is it possible to create a formula that would return values for a range between and including the end numbers. As an example:

=SUMIF(A2:A11,">=40000#OR#<=80000",B2:B11)

The result in this example would be 18,000

2. ## Re: SUMIF using between (97; SR2)

The following ARRAY formula should work...

{=SUM((A2:A11<=80000)*(A2:A11>=40000)*B2:B11)}

If unfamiliar with Array Formulas, enter starting with the "=" above and when complete press SHIFT+CTRL+ENTER to have Excel accept as an ARRAY formula. (it will add the "{}" automatically.

3. ## Re: SUMIF using between (97; SR2)

Thanks it works.

What about applying it to code. I've tried:

Var1 = Application.WorksheetFunction.Sum((Range(Cells(2, 1), Cells(11, 1)) <= 80000) * (Range(Cells(2, 1), Cells(11, 1)) >= 40000)) * Range(Cells(2, 2), Cells(11, 2))

And receive a "runtime error 13" - type mismatch

4. ## Re: SUMIF using between (97; SR2)

I don't think you can use arrays like that in VB:

Try something like:
<pre>Option Explicit
Function SumArray(rng As Range)
Dim rcell As Range
SumArray = 0
For Each rcell In rng
If rcell.Value <= 80000 _
And rcell.Value >= 40000 Then
SumArray = SumArray + rcell.Offset(0, 1).Value
End If
Next
End Function
</pre>

Steve

5. ## Re: SUMIF using between (97; SR2)

Steve,

I think that the array can be used in VB. If I'm able to use:

Var2 = Application.WorksheetFunction.SumIf(Range(Cells(2, 1), Cells(11, 1)), ">=40000", Range(Cells(2, 2), Cells(11, 2)))

with success to return the value 29,400. Then it would seem possible for Var1. Perhaps someone else can verify this.

John

6. ## Re: SUMIF using between (97; SR2)

Sorry for butting in again, but this is a non array entered formula

=SUMPRODUCT((A1:A10>=4000)*(A1:A10<=8000),B1:B10)

I don't know how to put this in code

7. ## Re: SUMIF using between (97; SR2)

This simlpe formula also works...

=SUMIF(A2:A11,"<=80000",B2:B11)-SUMIF(A2:A11,"<40000";B2:B11)

8. ## Re: SUMIF using between (97; SR2)

Sumif is not entered as an Array formula.

You are trying to use/enter sum as an array, I don't think THAT can be done in VB

Steve

9. ## Re: SUMIF using between (97; SR2)

Thanks to all who responded but I now have a new dilemma.

There seems to be a ghost value creeping into the function SumArray. I have attached a Zip file that contains the Excel file. If you sum the cells F5:F650 (these are for the accounts 70000 to 70170; inclusive) the result is \$547,728.95. However using the function SumArray, the result is \$639,175.59

<img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23>
What can I say...
John

10. ## Re: SUMIF using between (97; SR2)

The code for the SumArray function contains this:
If rcell.Value <= 71010 _
And rcell.Value >= 70000 Then

So it sums for accounts 70000 through 71010, i.e. F5:F993 instead of F5:F650

11. ## Re: SUMIF using between (97; SR2)

Hans,

I'm still getting a ghost number. I've attached a modified file. Note the yellow row which depicts the variance.

12. ## Re: SUMIF using between (97; SR2)

John,
<pre>Function SumArray(rngInput As Range, varCrit1, varCrit2, rngValues As Range)
Dim strCrit1 As String, strCrit2 As String
strCrit1 = ">=" & varCrit1
strCrit2 = "<=" & varCrit2
With Application.WorksheetFunction
SumArray = .SumIf(rngInput, strCrit1, rngValues) + _
.SumIf(rngInput, strCrit2, rngValues) - _
.Sum(rngValues)
End With
End Function
</pre>

so you now use it like this:
=sumarray(E5:E42,B2,B3,G5:G42)
where B2 and B3 contain the start and end points of your range (i.e. 70000 and 71070 in this case).
See attached for a reworked example.
HTH.
PS There is an anomaly in your workbook - you show a formula as using rows 5:607 but the corresponding sum function is using 5:603 so one of the checks doesn't work.

13. ## Re: SUMIF using between (97; SR2)

Rory,

What a great idea. I've modified the function slightly and got it to work on Sheet1 but it does not work on Sheet2. The cells with the new function SumArray2 are highlighted in green. I would like to refer back to the sheet using something like:

Sheets("Sheet1").Range((Cells(2,oCol), Cells(60000,oCol))

Where oCol = the column number of Sheet1 that I want the value from.

John

14. ## Re: SUMIF using between (97; SR2)

Rory already pointed out an inconsistency in one of your formulas; there are more discrepancies between the formulas in column A and column D.
But the cause of the "ghost" number is that you feed a far too large range to the SumArray function. The formulas in column A should look like =SumArray(E5:E12) etcetera, since you only need to sum values in column G (offset 2 from column E.)

In your other, more recent post: why don't you use Rory's function. It'll handle formulas with references to other sheets perfectly without any modification:

=SumArray(Sheet1!E5:E650,Sheet1!\$B\$2,Sheet1!\$B\$3,S heet1!G5:G650)

15. ## Re: SUMIF using between (97; SR2)

Hans,

In my previous post (with attachment-look at cell A30) I am using Rory's function with a slight modification to it. Within Rory's function Function SumArray(rngInput As Range, varCrit1, varCrit2, rngValues As Range) the columns associated with "rngValues " will be changing from time to time. Instead of manually changing the range each time, I thought it may be possible to do it through code ie ... Range((Cells(2,7), Cells(60000,7). I would then be able to change the "7" to the column number that I wanted information on. After creating a new function ie Function SumArray2 I did get it to work as long as the cell with the formula is in Sheet1. If I try it in Sheet2, it errors out.

Almost there..
John

Page 1 of 2 12 Last

#### Posting Permissions

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