Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 33
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
counting unique values conditionally jake Excel Discussion (Misc queries) 7 March 3rd 09 05:16 AM
how do i sort 2 colums so that the same values line up odi et amo Excel Discussion (Misc queries) 2 February 2nd 09 10:50 AM
Compare Sheets values in two colums Geir[_2_] Excel Worksheet Functions 1 April 7th 08 01:55 PM
Match name colums and paste corresponding values sumit Excel Discussion (Misc queries) 1 November 15th 06 12:58 PM
Sum values depending in values next column luiss Excel Discussion (Misc queries) 4 July 7th 06 05:30 AM


All times are GMT +1. The time now is 05:37 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"