How to conditionally sum values depending on values in other colums

[Wim Bartsoen]

Hello,

I have a worksheet called "FY04 Requests" which records
per row a training request by an individual. There can be
many training requests by one individual. I'm trying to
figure out how I can write a function to show the total
value per individual and per quarter of approved requests
on a different worksheet.

I currently have what's below. But for some reason the
first .Range(B2:K100) call keeps on returning nothing. The
Function is defined in a module next to some recorded
Macros.

Function Calculate_Used_Budget_By_Quarter(FullName As
String, Quarter As String) As Double
'
' Calculate_Used_Budget_By_Quarter Function
' Added by Wim Bartsoen on 27/10/2003
'
' Purpose: This function calculates the total training
' budget used by a member of the specialty by doing
' an SQL like query on the FY04 Requests worksheet.
' The following parameters are passed to it:
' - Name of the requestor
' - Quarter of the request (accepted arguments are Q1,
Q2, Q3 and Q4)
'
' Dependencies:
' * The function takes the whole of the sheet
entitled "FY04 Requests"
' as its database
' * The keyword that needs to be matched is "Approved"
' * It assumes the amounts to sum are in Column J
' * It assumes that the date to compare is in Column G
' * It makes some assumptions about the sheet layout to
improve speed

Const SearchParam1 As String = "Approved"

Dim StartDate As Date
Dim rng As Range
Dim Sum As Double

Sum = 0
rng = ActiveWorkbook.Worksheets("FY04 Requests").Range
("B2:K100")
For n = 1 To rng.Rows.Count
If rng.Cells(n, 2).Value = "Approved" _
And rng.Cells(n, 3).Value = FullName Then
Sum = Sum + rng.Cells(n, 10).Value
End If
Next
Calculate_Used_Budget_By_Quarter = Sum

End Function

Can someone point me at what is going wrong here?

Any help is greatly appreciated.

Wim

 

[Tom Ogilvy]

rng = needs to be set rng =
since you anchor on rng and rng starts in column 2, J is in column 9

? Range("B1").cells(1,9).Address
$J$1
You could have a similar problem with rng.Cells(n,2) which will be column C

You never check against any date, so it isn't restricted to a specific
quarter.

That was all I noticed right off. There may be other problems.

Const SearchParam1 As String = "Approved"

Dim StartDate As Date
Dim rng As Range
Dim Sum As Double

Sum = 0
rng = ActiveWorkbook.Worksheets("FY04 Requests").Range
("B2:K100")
For n = 1 To rng.Rows.Count
If rng.Cells(n, 2).Value = "Approved" _
And rng.Cells(n, 3).Value = FullName Then
Sum = Sum + rng.Cells(n, 10).Value
End If
Next
Calculate_Used_Budget_By_Quarter = Sum

End Function

 

[Chrissy]

Function Calculate_Used_Budget_By_Quarter(FullName As String, Quarter As String) As Double
Const SearchParam1 As String = "Approved"
Dim RNG As Range
Dim dSum As Double
Dim C As Range

dSum = 0

For Each C In ActiveWorkbook.Worksheets("FY04 Requests").Range("B2:B100")
If C.Offset(0, 3) = FullName _
And C.Offset(0, 2) = SearchParam1 Then
dSum = dSum + CDbl(C.Offset(0, 10))
End If
Next C
End Function


Try that. You will need to add to it for the date test, which you include in your
comments but not in your code. Also, check the columns as I am not sure if
2 and 10 are the ones you want.

Chrissy.