Count if query

burdie87

Board Regular
Joined
May 25, 2005
Messages
152
Hey guys and girls,

Hopefully a quick one for all you wizz kids on excel.

Is it possible to make an if statement in a cell that only adds the cells in a collum if they are not struck through.

I use this for when an order is complete and would like my total at the bottom of the column to change when I strike out an order in the list.

Any ideas will be appreciated.

Regards

Alan.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Hi Alan

Because this conditional sum is dependent on the formatting of the cells, you need VBA to do this (ie a custom User-Defined Function). How happy with the prospect of using VBA are you?
 
Upvote 0
Try this code:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Limit As Long
Dim myTotal As Double
Dim myRange As Range
Dim r As Range
myTotal = 0
Limit = Cells(Rows.Count, 1).End(xlUp).Row
Set myRange = Range("A1:A" & Limit)
For Each r In myRange
    If r.Row <> 1 Then
        If r.Value = "Total" Then Cells(r.Row, 2) = myTotal
        If r.Font.Strikethrough = False Then
            myTotal = myTotal + Range("B" & r.Row)
        End If
    End If
Next r
End Sub

Based on a worksheet that looks like this:
Excel Workbook
ABC
1ItemNumber
2type220
3type117
4type325
5
6Total45
7
Sheet2
 
Upvote 0
Hi,

Three questions for you.

1) where should I be placing this code?

2) if I want to edit the code to work in column J how would I do this.

N.B. I am striking through the figures themself not the text in the column before is it possible to make work from the strike through the numbers

3) Is it possible to make the code work for another total on the same sheet??


I do appreciate your help in this.

Alan.
 
Upvote 0
1) Right click the worksheet tab and select “View Code”. Paste the code in the main window that opens up.

2) The part which determines which column it is checking for the strikethrough’s in is this:
Code:
Limit = Cells(Rows.Count, 1).End(xlUp).Row 
Set myRange = Range("A1:A" & Limit)
To change this to look through a different column, change the “1” in the first line to the number of the column in which you are performing the strikethrough (column J would be 10).
In the second line change both of the “A’s” to “J”.

The part which deals with totalling up the values is this:
Code:
If r.Value = "Total" Then Cells(r.Row, 2) = myTotal 
        If r.Font.Strikethrough = False Then 
                myTotal = myTotal + Range("B" & r.Row)
In line 1 change the “2” to the number of the column in which the word “Total” appears at the bottom of the list.
In line 3, change the “B” to the letter of the column in which your values are.

3) It is entirely possible to duplicate this code for another total on the worksheet by simply copying and pasting the code again (not the first and last lines, just the rest of it pasted again between the first and last lines) making the appropriate adjustments as above to the locations.
 
Upvote 0
Ok,

Hit a snag.

the current code I am using is as follow:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Limit As Long
Dim myTotal As Double
Dim myRange As Range
Dim r As Range
myTotal = 0
Limit = Cells(Rows.Count, 10).End(xlUp).Row
Set myRange = Range("J1:J" & Limit)
For Each r In myRange
    If r.Row <> 1 Then
        If r.Value = "Total" Then Cells(r.Row, 9) = myTotal
        If r.Font.Strikethrough = False Then
            myTotal = myTotal + Range("J" & r.Row)
        End If
    End If
Next r
End Sub

From the above codes my information is in the following areas.
Figures with scores through are in Row J
My total is in row J
My Text saying total is in row I

I would also if possible like to have the text saying total as "current order book" but this is not too important.

Thanks again.

Alan.
 
Upvote 0
Ok.

I have done some further investigating and have found that the code does not seem to work if you try and do everything in the same column using your walk through on how to change the variables.

I tested it on the example you gave and no matter what I do I cannot get it to work.

Any help would be much appreciated.

Thanks.
 
Upvote 0
Hi,

UDF.

Code:
Function BURDIE(r As Range) As Double
Dim Rng As Range, v   As Range
Set Rng = r.SpecialCells(xlCellTypeConstants, 1)
For Each v In Rng
    If Not v.Font.Strikethrough Then BURDIE = BURDIE + v.Value
Next
End Function

This goes in Standard module.

Use like

=BURDIE(A1:A10)

HTH
 
Upvote 0
Excellent Kris.

This works a treat.

Is there a way however of activating it using the worksheet_change function as the only way I can get it to update is by double clicking the formula cell and then pressing the green tick.

If you can think of a better way of doing this I would be grateful.

Alan.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top