Sum Formula that Includes SUMIF and Excludes StrikeThrough:

[MattDW]

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!

 

[MattDW]

Re:

In case it helps, here's a simpler explanation of what I am hoping to accomplish (I probably over complicated my explaination the first time around).

Does anybody know of a way to calculate a total that both uses the strikethrough exclusion formula: =STRIKESUM(A1:A10) [borrowed from here: http://www.mrexcel.com/board2/viewtopic.php?t=129445] and the SUMIF formula at the same time? Is there a way in Excel to embed or combine formulas?

Basically what I'd like to do (for the box at the bottom of the spreadsheet) is to generate two totals for column H. The first should exclude all struck-out numbers but should only include numbers where the value in column E equals Mill Levy. The second should exclude all struck-out numbers but should only include numbers where the value in column E equals Bond. I believe the formula =SUMIF(E6:E95,"Mill Levy",H6:H95) and =SUMIF(E6:E95,"Bond",H6:H95) should do the later part. Problem is, I'm not quite sure how to accomplish both at the same time.

Thanks much!

 

[Fazza]

Hi Matt,

It isn't very clear what you want. Can you please post a picture? Or a few pictures even if you need to.

BTW, you can do the sum of the struck through items without VBA but lets just focus on the question at hand.

regards, Fazza

 

[MattDW]

Re:

Hi Matt,

It isn't very clear what you want. Can you please post a picture? Or a few pictures even if you need to.

BTW, you can do the sum of the struck through items without VBA but lets just focus on the question at hand.

regards, Fazza

Thanks Fazza for your help,

I've uploaded screen captures at: http://staffweb.psdschools.org/mway/explaination.doc

Hopefully this makes clearer what I am hoping for.

I suppose another way I could say this is I am looking to use the formula =STRIKESUM(H6:H95) and =SUMIF(E6:E95,"Mill Levy",H6:H95) formulas together, at the same time, to give me a unique total for column H. Each formula alone gives me a correct total but I have no idea (since Excel only allows one formula per cell) how I might (or if I can) combine the two of these to give me a sum that is the combination of the two.

I tried embedding the two, like so: =STRIKESUM(SUMIF(E6:E95,"Mill Levy",H6:H95)) but received errors -- so I'm guessing Excel had no idea what I meant by that. But basically that's the idea I am going for, creating a formula that sums only the struckthrough cells in column H while also only summing the cells that have a corresponding value of "Mill Levy" in column E.

Thanks and if this still doesn't make sense, feel free to write me back and I'll try and approach/explain it from a different angle.

Thanks for your help, Fazza!

 

[Fazza]

Matt,

You've done a really great job with the explanation document. It makes it 100 times easier to help you.

I'm going to suggest a slight modification to your approach, as follows. Populate column "A" with either TRUE or FALSE entries for each row, A6:A95. Header cell A5 would be something like "Approved". Then you can use conditional formatting in fields "Dept", "Proposal Request", "Contact", "Amount Per Unit", "Number of Units" and "Total $ Amount Requested" to automatically create the strikethrough effect based on the TRUE/FALSE entries in the new "Approved" field.

And the TRUE/FALSE can be used with normal SUMIF formulas for your totals. Or an array formula when you want to use two criteria - the TRUE/FALSE and "Mill Levy"/"Bond".

[As an aside, I would favour a different structure with a data worksheet like you have in range "B5:G95" and run all reports on different worksheets. Probably with a pivot table or whatever. The structure currently mixes up the data with the reporting.]

Now to address specifics of your requirements assuming you use column "A" with TRUE or FALSE entries for approved.

Strikethrough is achieved by then highlighting the cells to coniditionally have strikethrough, such as "B6:D95,F6:H95". Hold CTRL to select multiple areas at once. I'll assume the active cell is "B6". Remove all existing strikethrough format. Add new conditional format via menu Format, Conditional Format, then formula; make it without the double quotes "=NOT($A6)" and set the format to strikethrough. OK to that and you should be right. Now when there is TRUE in column A, the entries in that row will be normal font and when column A contains FALSE, the conditional format will cause them to show with strikethrough.

Now your totals by department in column "I" can be done in a few ways. By way of example, one is with SUMIF(A6:A11,TRUE,H6:H11).

And your column "H" column total in cell H96 can be SUMIF(A6:A95,TRUE,H6:H95)

Now for the two conditions, Approved=TRUE and 'type' is 'Mill Levy', in cell C105 use SUM(A6:A95,E6:E95="Mill Levy",H5:H95) entered with CTRL-SHIFT-ENTER to make it an array formula.

And in cell C106 use SUM(A6:A95,E6:E95="Bond",H5:H95) entered with CTRL-SHIFT-ENTER to make it an array formula.

An aside, you can multiply the TRUE/FALSE entries by say your column H entries. The TRUE values will multiply as 1 and the FALSE as zero. Then you can sum on these.

HTH, Fazza

 

[MattDW]

Re:

Matt,

You've done a really great job with the explanation document. It makes it 100 times easier to help you.

I'm going to suggest a slight modification to your approach, as follows. Populate column "A" with either TRUE or FALSE entries for each row, A6:A95. Header cell A5 would be something like "Approved". Then you can use conditional formatting in fields "Dept", "Proposal Request", "Contact", "Amount Per Unit", "Number of Units" and "Total $ Amount Requested" to automatically create the strikethrough effect based on the TRUE/FALSE entries in the new "Approved" field.

And the TRUE/FALSE can be used with normal SUMIF formulas for your totals. Or an array formula when you want to use two criteria - the TRUE/FALSE and "Mill Levy"/"Bond".

[As an aside, I would favour a different structure with a data worksheet like you have in range "B5:G95" and run all reports on different worksheets. Probably with a pivot table or whatever. The structure currently mixes up the data with the reporting.]

Now to address specifics of your requirements assuming you use column "A" with TRUE or FALSE entries for approved.

Strikethrough is achieved by then highlighting the cells to coniditionally have strikethrough, such as "B6:D95,F6:H95". Hold CTRL to select multiple areas at once. I'll assume the active cell is "B6". Remove all existing strikethrough format. Add new conditional format via menu Format, Conditional Format, then formula; make it without the double quotes "=NOT($A6)" and set the format to strikethrough. OK to that and you should be right. Now when there is TRUE in column A, the entries in that row will be normal font and when column A contains FALSE, the conditional format will cause them to show with strikethrough.

Now your totals by department in column "I" can be done in a few ways. By way of example, one is with SUMIF(A6:A11,TRUE,H6:H11).

And your column "H" column total in cell H96 can be SUMIF(A6:A95,TRUE,H6:H95)

Now for the two conditions, Approved=TRUE and 'type' is 'Mill Levy', in cell C105 use SUM(A6:A95,E6:E95="Mill Levy",H5:H95) entered with CTRL-SHIFT-ENTER to make it an array formula.

And in cell C106 use SUM(A6:A95,E6:E95="Bond",H5:H95) entered with CTRL-SHIFT-ENTER to make it an array formula.

An aside, you can multiply the TRUE/FALSE entries by say your column H entries. The TRUE values will multiply as 1 and the FALSE as zero. Then you can sum on these.

HTH, Fazza

Thanks Fazza . I'll give this a go.

 

[MattDW]

Re:

Matt,

You've done a really great job with the explanation document. It makes it 100 times easier to help you.

I'm going to suggest a slight modification to your approach, as follows. Populate column "A" with either TRUE or FALSE entries for each row, A6:A95. Header cell A5 would be something like "Approved". Then you can use conditional formatting in fields "Dept", "Proposal Request", "Contact", "Amount Per Unit", "Number of Units" and "Total $ Amount Requested" to automatically create the strikethrough effect based on the TRUE/FALSE entries in the new "Approved" field.

And the TRUE/FALSE can be used with normal SUMIF formulas for your totals. Or an array formula when you want to use two criteria - the TRUE/FALSE and "Mill Levy"/"Bond".

[As an aside, I would favour a different structure with a data worksheet like you have in range "B5:G95" and run all reports on different worksheets. Probably with a pivot table or whatever. The structure currently mixes up the data with the reporting.]

Now to address specifics of your requirements assuming you use column "A" with TRUE or FALSE entries for approved.

Strikethrough is achieved by then highlighting the cells to coniditionally have strikethrough, such as "B6:D95,F6:H95". Hold CTRL to select multiple areas at once. I'll assume the active cell is "B6". Remove all existing strikethrough format. Add new conditional format via menu Format, Conditional Format, then formula; make it without the double quotes "=NOT($A6)" and set the format to strikethrough. OK to that and you should be right. Now when there is TRUE in column A, the entries in that row will be normal font and when column A contains FALSE, the conditional format will cause them to show with strikethrough.

Now your totals by department in column "I" can be done in a few ways. By way of example, one is with SUMIF(A6:A11,TRUE,H6:H11).

And your column "H" column total in cell H96 can be SUMIF(A6:A95,TRUE,H6:H95)

Now for the two conditions, Approved=TRUE and 'type' is 'Mill Levy', in cell C105 use SUM(A6:A95,E6:E95="Mill Levy",H5:H95) entered with CTRL-SHIFT-ENTER to make it an array formula.

And in cell C106 use SUM(A6:A95,E6:E95="Bond",H5:H95) entered with CTRL-SHIFT-ENTER to make it an array formula.

An aside, you can multiply the TRUE/FALSE entries by say your column H entries. The TRUE values will multiply as 1 and the FALSE as zero. Then you can sum on these.

HTH, Fazza

Thanks Fazza . I'll give this a go.

Hi Fazza,

Well, I was expecting something like array formulas to be able to do something like this. I followed your instructions, setting up another column (I did column B instead of A -- since A is my margin and adjusted the formula column letters accordingly, as when inserting another column, all other cells shifted forward one letter).

The first snag I ran into was with the column I formulas. Excel doesn't seem to allow me to add an array formula into a merged cell, as is the case with column I.

The second problem I ran into is the total formula of =SUMIF(B6:B95,TRUE,I6:I95) in cell I96 returns me the value of $140,682.11. The total formula of =SUMIF(B6:B95,TRUE,J6:J95) in cell J96 returns me $141,501.38. These two values should be the same and the numbers I received with my old formula of =STRIKESUM(H6:H95) (the sum which is correct) is $141,127.31.

The third problem I ran into was with the Total Mill Levy Requests: formula in C105 returns me the number $273,688.31. This number is $273,688.31. This is the same number I'd receive if I were to do a SUM(I5:I95). This initially lead me to believe that the formula might be doing a SUMIF but otherwise was ignoring the strikeout. However, in changing the value of one of the cells in column F from Mill Levy to Bond did not change this formula (I have VBA code that refreshes all formulas, including array formulas, on Worksheet_SelectionChange), even when selecting the formula and Ctrl + Shift + Entering to refresh it.

Any ideas if I am doing something wrong or if something else is going on? I'll try to get some screen captures up for you again, if you'd like, as I've already taken the previously posted Word doc off the web server.

Also, any possible way to create an array formula without needing the extra column B (the TRUE/FALSE column)? It's slick how conditional formatting allows true/false entries to translate into strikeout but being that this is also something I need to occasionally print out to share with our committee, I am trying to keep the information and number of columns on the spreadsheet to a minimum. Then again, maybe that isn't possible?

Thanks much!