Results 1 to 15 of 18
Thread: SUMIF using between (97; SR2)

20030304, 17:46 #1
 Join Date
 Dec 2000
 Location
 California, USA
 Posts
 1,758
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20030304, 18:21 #2
 Join Date
 Jun 2001
 Posts
 76
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20030304, 19:55 #3
 Join Date
 Dec 2000
 Location
 California, USA
 Posts
 1,758
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20030304, 20:16 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20030304, 21:48 #5
 Join Date
 Dec 2000
 Location
 California, USA
 Posts
 1,758
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20030305, 05:02 #6
 Join Date
 Feb 2003
 Posts
 89
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20030305, 06:44 #7
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUMIF using between (97; SR2)
This simlpe formula also works...
=SUMIF(A2:A11,"<=80000",B2:B11)SUMIF(A2:A11,"<40000";B2:B11)Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20030305, 11:31 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20030306, 00:51 #9
 Join Date
 Dec 2000
 Location
 California, USA
 Posts
 1,758
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20030306, 01:10 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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

20030306, 16:50 #11
 Join Date
 Dec 2000
 Location
 California, USA
 Posts
 1,758
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20030306, 18:38 #12
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,291
 Thanks
 3
 Thanked 198 Times in 184 Posts
Re: SUMIF using between (97; SR2)
John,
I've made your function a little more generic:
<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.Regards,
Rory
Microsoft MVP  Excel

20030306, 20:44 #13
 Join Date
 Dec 2000
 Location
 California, USA
 Posts
 1,758
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.
Thanks for your time.,
John

20030306, 21:20 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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)

20030306, 22:22 #15
 Join Date
 Dec 2000
 Location
 California, USA
 Posts
 1,758
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUMIF using between (97; SR2)
Hans,
In my previous post (with attachmentlook 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