Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to conditionally sum values depending on values in other colums
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to conditionally sum values depending on values in other colums
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 -- Regards, Tom Ogilvy "Wim Bartsoen" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to conditionally sum values depending on values in other colums
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. "Wim Bartsoen" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting unique values conditionally | Excel Discussion (Misc queries) | |||
how do i sort 2 colums so that the same values line up | Excel Discussion (Misc queries) | |||
Compare Sheets values in two colums | Excel Worksheet Functions | |||
Match name colums and paste corresponding values | Excel Discussion (Misc queries) | |||
Sum values depending in values next column | Excel Discussion (Misc queries) |