CODEWITHSUNDEEP
excelexcel-formula
Moosli
Moosli

Reputation: 3285

Counting Filterd Range with Multiplication Condition

I need to Count the Partial result of all Address in Column A.

enter image description here

if the "T=" Value in Column A Contains a Semikolon it hase to Count double, if it Contains 2 Semikolons it hase to count 3 and so on...

Her is a Picture to show how the Counting should work.

I Have The Formulas to Count the Amount where the Status is Finished or Pending and the Formula to Count the Total Amount.

Shown in this Pictures. enter image description here

You can see that the Partial Result is 22 like the Total Amount, because there is no Filter.

When i set a Filter it should show only the Amount of the Visible Cells, LIke in this Picture.

enter image description here

Upvotes: 0

Views: 37

Answers (1)

TBlock
TBlock

Reputation: 71

UPDATE:
After rooting around some more, I found a trick that should do it. It needs to be entered as an array function (Ctrl+Shift+Enter).

=SUMPRODUCT((LEN(Range)-LEN(SUBSTITUTE(Range,";","")) +1)*--(SUBTOTAL(103,OFFSET(FirstCell,ROW(Range)-ROW(FirstCell),0))=1))

In your case, FirstCell is A7 and Range is A7:A22.

Subtotal(103,...) is a CountA function, but it ignores hidden cells. However, it only returns one value (the number of hidden cells) unless it is given an array of references, which is what the Offset nonsense provides.
Note: This question is quite similar to this one.

Let me know if this does the trick:

Function CountFilter(rng As Range, delimiter As String) As Integer
    CountFilter = 0
    For Each c In rng
        If Rows(c.Row).Hidden = False Then
            CountFilter = CountFilter + 1 + CountChrInString(c.Value, delimiter)
        End If
    Next c
End Function

Public Function CountChrInString(Expression As String, Character As String) As Long
''
''' Returns the count of the specified character in the specified string.
'''
'
' ? CountChrInString("a/b/c", "/")
'  2
' ? CountChrInString("a/b/c", "\")
'  0
' ? CountChrInString("//////", "/")
'  6
' ? CountChrInString(" a / b / c ", "/")
'  2
' ? CountChrInString("a/b/c", " / ")
'  0
'
    Dim iResult As Long
    Dim sParts() As String

    sParts = Split(Expression, Character)

    iResult = UBound(sParts, 1)

    If (iResult = -1) Then
    iResult = 0
    End If

    CountChrInString = iResult

End Function

CountChrInString function copied shamelessly from here.

Upvotes: 1

Related Questions