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.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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?
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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
 

burdie87

Board Regular
Joined
May 25, 2005
Messages
152

ADVERTISEMENT

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.
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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.
 

burdie87

Board Regular
Joined
May 25, 2005
Messages
152

ADVERTISEMENT

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.
 

burdie87

Board Regular
Joined
May 25, 2005
Messages
152
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.
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
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
 

burdie87

Board Regular
Joined
May 25, 2005
Messages
152
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,411
Messages
5,595,985
Members
414,035
Latest member
billbumkins

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
Top