Hi,
I have an Excel spreadsheet that keeps track of all of the Bond and Mill Levy requests for our building. If you will bare with me, I'll give you a brief overview of what this spreadsheet looks like: This spreadsheet uses columns B:I and rows 5 through 135 and is laid out as such: Column B contains the department name making the request, Column C contains the request being made, Column D contains the contact person who made the request (usually a department head), Column E contains the budget that this request will come out of (either Mill Levy or Bond), Column F contains the amount per unit, Column G contains the number of units, column H contains the total amount (per unit * number of units ex., F6*G6), Column I contains the total amount by department and has merge turned on to span the number of rows used per department. Because our committee wanted to show items requested but ultimately rejected on this spreadsheet I am using some VBA code suggested by Von Pookie here: http://www.mrexcel.com/board2/viewtopic.php?t=129445. The below VBA code is in Module1 of my spreadsheet and the formula: =STRIKESUM(H6:H11) is in Column I (Total $ Amount By Department). This allows us to sum only non-struck lines on my spreadsheet:
Public Function STRIKESUM(ByVal myRange As Range)
Dim cell As Range, x As Integer
For Each cell In myRange
If cell.Font.Strikethrough = False Then x = x + cell
Next cell
STRIKESUM = x
End Function
In order to recalculate formulas on the fly I am also using the VBA code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oSht As Worksheet
Application.Calculation = xlCalculationManual
For Each oSht In Worksheets
oSht.EnableCalculation = False
oSht.EnableCalculation = True
Next oSht
Application.Calculate
End Sub
At the bottom of the spreadsheet (row 96, columns H and I) I have two totals, a total for column H (total for the total amount by unit): =STRIKESUM(H6:H95) and a total for Column I (total for total amount by department): =STRIKESUM(I6:I95).
In Column B, rows 98 through 108, I have the following labels with the corresponding numbers or formulas in Column C: "Milly Levy Carry Over:, Bond Carry Over:, Mill Levy, Bond:, Total Mill Levy (With Carry Over): =C98+C100, Total Bond (With Carry Over): =C99+C101, Bond and Mill Levy Together: =C102+C103, Total Requests For Mill Levy:, Total Requests for Bond:, Total Remaining Mill Levy: =C102-C105, Total Remaining Bond: =C103-C106.
It is in this section of the spreadsheet that I am having difficulty. Basically what I would like to have happen for the formula for Total Requests For Mill Levy and Total Requests for Bond is to have Excel sum the values in H6:H95 but I'd like it to exclude any values struck out (the formula =STRIKESUM(H6:H95) could do that) and have it only include that sum in the row if the value of column E equals Mill Levy (ignoring any rows containing the value of Bond). I believe something like =SUMIF(E6:E95,Mill Levy,H6:H95) can do this? The thing I'm not sure how to do is to combine the two of these together in order to have a sum that evaluates both criteria at the same time. Is there a way to embed two formulas in Excel or is this something that can be taken care of using VBA?
Thanks much!
I have an Excel spreadsheet that keeps track of all of the Bond and Mill Levy requests for our building. If you will bare with me, I'll give you a brief overview of what this spreadsheet looks like: This spreadsheet uses columns B:I and rows 5 through 135 and is laid out as such: Column B contains the department name making the request, Column C contains the request being made, Column D contains the contact person who made the request (usually a department head), Column E contains the budget that this request will come out of (either Mill Levy or Bond), Column F contains the amount per unit, Column G contains the number of units, column H contains the total amount (per unit * number of units ex., F6*G6), Column I contains the total amount by department and has merge turned on to span the number of rows used per department. Because our committee wanted to show items requested but ultimately rejected on this spreadsheet I am using some VBA code suggested by Von Pookie here: http://www.mrexcel.com/board2/viewtopic.php?t=129445. The below VBA code is in Module1 of my spreadsheet and the formula: =STRIKESUM(H6:H11) is in Column I (Total $ Amount By Department). This allows us to sum only non-struck lines on my spreadsheet:
Public Function STRIKESUM(ByVal myRange As Range)
Dim cell As Range, x As Integer
For Each cell In myRange
If cell.Font.Strikethrough = False Then x = x + cell
Next cell
STRIKESUM = x
End Function
In order to recalculate formulas on the fly I am also using the VBA code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oSht As Worksheet
Application.Calculation = xlCalculationManual
For Each oSht In Worksheets
oSht.EnableCalculation = False
oSht.EnableCalculation = True
Next oSht
Application.Calculate
End Sub
At the bottom of the spreadsheet (row 96, columns H and I) I have two totals, a total for column H (total for the total amount by unit): =STRIKESUM(H6:H95) and a total for Column I (total for total amount by department): =STRIKESUM(I6:I95).
In Column B, rows 98 through 108, I have the following labels with the corresponding numbers or formulas in Column C: "Milly Levy Carry Over:, Bond Carry Over:, Mill Levy, Bond:, Total Mill Levy (With Carry Over): =C98+C100, Total Bond (With Carry Over): =C99+C101, Bond and Mill Levy Together: =C102+C103, Total Requests For Mill Levy:, Total Requests for Bond:, Total Remaining Mill Levy: =C102-C105, Total Remaining Bond: =C103-C106.
It is in this section of the spreadsheet that I am having difficulty. Basically what I would like to have happen for the formula for Total Requests For Mill Levy and Total Requests for Bond is to have Excel sum the values in H6:H95 but I'd like it to exclude any values struck out (the formula =STRIKESUM(H6:H95) could do that) and have it only include that sum in the row if the value of column E equals Mill Levy (ignoring any rows containing the value of Bond). I believe something like =SUMIF(E6:E95,Mill Levy,H6:H95) can do this? The thing I'm not sure how to do is to combine the two of these together in order to have a sum that evaluates both criteria at the same time. Is there a way to embed two formulas in Excel or is this something that can be taken care of using VBA?
Thanks much!