ISO: Function to count cells depending on contents

Shadal

New Member
Joined
Sep 25, 2021
Messages
1
Could someone help me create a function please?

I would like a cell to give me a count based on the contents of a column.

I have the following column. If the cell contains "N" it should add 1 to the count. If the cell contains a name, it should add 0.5 to the count. If the cell is strikethrough it should be ignored.

1632612646532.png


Thanks in advance!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
This counts cells in the range R that contain only "N", or other text w/o strikethrough. It ignores blank cells and cells with an error, if any.
NOTE: values in red font have strikethrough which doesn't seem to be captured by XL2BB.
Book1
ABC
1Split11
2Megan
3N
4
5#N/A
6N
7N
8N
9N
10N
11N
12Kevin
13N
14N
15N
16N
17N
18Blake
Sheet1
Cell Formulas
RangeFormula
C1C1=countem(A2:A18)
A5A5=NA()

VBA Code:
Function Countem(R As Range) As Double
Dim c As Range
For Each c In R
    If Not IsEmpty(c) Then
        If Not c.Font.Strikethrough And Not IsError(c.Value) Then
            If c.Value = "N" Then
                Countem = Countem + 1
            Else
                Countem = Countem + 0.5
            End If
        End If
    End If
Next c
End Function
 
Upvote 0

Forum statistics

Threads
1,214,944
Messages
6,122,392
Members
449,081
Latest member
JAMES KECULAH

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