Forum Discussion
Hey!
i need help for creating a code, Count several variables in the same column.
like 1R, 1B, 1G and so on thank you!
If combine mtarler formulas in one
=FILTER(IF({1,0},SORT(UNIQUE(LEFT($A$2:$A$1500,LEN($A$2:$A$1500)-1))), COUNTIFS($A$2:$A$1500,SORT(UNIQUE(LEFT($A$2:$A$1500,LEN($A$2:$A$1500)-1)))&"?")), COUNTIFS($A$2:$A$1500,SORT(UNIQUE(LEFT($A$2:$A$1500,LEN($A$2:$A$1500)-1)))&"?") >0)
34 Replies
killerBee615 , do you mean something like this?
TheAntony , not exactly like that, in the same column same numbers with different letters.
like
1r 1b 1c 1g
2e 2f 2g
then the count will be total 3 times 2
and so on with 3-99999
and than the count will count all the same numbers with different letters.
killerBee615 i think I understand what you want. you want to know how many different numbers there are in the column if you ignore the letters.
The best I got is if you create a helper column using this formula to strip off the letters:
=LEFT(A1,SUM(--ISNUMBER(--MID(A1,{1,2,3,4,5},1))))but that assumes a) a maximum of 99999 (i.e. 5 digits) and b) there are no numbers after the letters start. Other versions of this formula are possible if needed.
Once you have this 'helper' column then you can simply use:
=COUNTA(UNIQUE(B:B))to count how many unique numbers exist in that helper column.
- On the fast, a code to try.
Just have to adjust to your needs 🙂
Sub Summ()
Dim sh As Worksheet
Dim rngFind As Range
Dim strSuchen As String
Dim sngSum As Single
strSuchen = InputBox("Nummer?")
If strSuchen = "" Then Exit Sub
For Each sh In Worksheets
Set rngFind = sh.Cells.Find(strSuchen, Cells(1, 1))
If Not rngFind Is Nothing Then
If IsNumeric(rngFind.Offset(0, 1).Value) Then
sngSum = sngSum + rngFind.Offset(0, 1).Value
End If
Set rngFind = Nothing
End If
Next sh
MsgBox sngSum
End Sub
Nikolino
I know I don't know anything (Socrates)Maybe something very simple ... even if the formulas from the others are much better than these
Nikolino
I know I don't know anything (Socrates)
