Formula to exclude strikethrough text from being counted
Dubanne
Posted messages
3
Status
Member
-
JvDo Posted messages 1924 Registration date Status Member Last intervention -
JvDo Posted messages 1924 Registration date Status Member Last intervention -
Hello,
Here is my problem: I need to count the number of transports while excluding the crossed-out ones as those are absent persons. Currently, I am using
=COUNTIF(data range, "name")
However, in my table, I have crossed-out transports if the persons are not coming. So my formula is incomplete. I've been looking for a while now, but I can't figure it out, and that's why I'm asking for your help.
I would love to send you my table as an example, but apparently, I can't. I managed to attach a photo if that can help you.
Thank you so much for your assistance.
Best regards
Here is my problem: I need to count the number of transports while excluding the crossed-out ones as those are absent persons. Currently, I am using
=COUNTIF(data range, "name")
However, in my table, I have crossed-out transports if the persons are not coming. So my formula is incomplete. I've been looking for a while now, but I can't figure it out, and that's why I'm asking for your help.
I would love to send you my table as an example, but apparently, I can't. I managed to attach a photo if that can help you.
Thank you so much for your assistance.
Best regards
4 answers
Hello,
To make a file available to us:
Go to the site: www.cijoint.com
upload your file
give us the link here.
As for your question... I don't think we can do what you want..
A Strikethrough text... it's "just" formatting.... there's nothing in the cell that allows it to be identified...
Why don't you add a Present/Absent column next to it.. where you would put 1s or 0s...
You would then only need to create your formula based on that column.
Otherwise.. you can use VBA:
In a module, place the code
Then in your sheet, replace your NB.SI with:
--
Before asking a question, please read the site’s charter.
Best regards, Jordane
To make a file available to us:
Go to the site: www.cijoint.com
upload your file
give us the link here.
As for your question... I don't think we can do what you want..
A Strikethrough text... it's "just" formatting.... there's nothing in the cell that allows it to be identified...
Why don't you add a Present/Absent column next to it.. where you would put 1s or 0s...
You would then only need to create your formula based on that column.
Otherwise.. you can use VBA:
In a module, place the code
Function NbCellNonBarreesSI(rng As Range, critere As Range) As Long Nb = 0 For Each cell In rng test = cell.Value If Not cell.Font.Strikethrough And cell.Value = critere.Value Then Nb = Nb + 1 End If Next NbCellNonBarreesSI = Nb End Function
Then in your sheet, replace your NB.SI with:
=NbCellNonBarreesSI(B2:B10;B13)
--
Before asking a question, please read the site’s charter.
Best regards, Jordane
Thank you for your attention and your response.
I tried this method but it doesn't work. I keep looking.
As you said, there is no solution to my problem.
I will try to find a solution regarding conditional formatting and see if I can come up with a formula to leave the cell empty rather than strikethrough... My current formula is:
=$T5>"" . Do you have any ideas?
Thank you again in advance and I wish you happy holidays.
Best regards
there must be an error in handling.
best regards