Hey!

[killerBee615]

i need help for creating a code, Count several variables in the same column.

like 1R, 1B, 1G and so on thank you!

[#2]

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)

[#3]

@killerBee615 , do you mean something like this? 

[#4]

@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. 

[#5]

@NikolinoDE 

thanks for the help!.

But i didn't understand how to do it.

[#6]

No problem, is in German VBA, should actually be converted to English. Whichever solution has helped you please tick as correct answer so that others can get this info.

Nikolino
I know I don't know anything (Socrates)

[#7]

@killerBee615 , it's hard to visualize your words. Can you attach a sample worksheet (with all confidential info removed)?

[#8]

@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. 

[#9]

@TheAntony  hope it will explain what i mean

[#10]

@killerBee615  based on this I believe you will always have and only have 1 letter following the product number, which makes the formula much easier.  but if that isn't the case, we can still work with that.  I used the UNIQUE() formula to produce a list of all the products and then a counts column for each of those product numbers:

=SUMPRODUCT(--IFERROR(LEFT(A:A,LEN(A:A)-1)=C2,0))

see attached

[#11 · Accepted solution]

@killerBee615 

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)

[#12]

@killerBee615 

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)

[#13]

it's work fast and exactly like i want to. but how i expand it, if i want much bigger range of numbers?

[#14]

@killerBee615 

I am not sure if this is meant ... please have a look and if it is not what you are looking for, please give me a little feedback.


Nikolino
I know I don't know anything (Socrates)

[#15]

@killerBee615 

You may use another range instead of $A$2:$A$1500 or use dynamic range like A2:INDEX(A:A,COUNTA(A:A))

[#16]

@Sergei Baklan i meant to the kinds of the number it counts 1-3,i want that it will count 1-100 how can i expand it. the code to hard for to understand how to do it myself.

thanks for the help

[#17]

Hey i don't need it will count the letters, i need it will count the numbers without the letters.
And i need it will be in column not in line

[#18]

The sheet I sent you last counts the numbers that can be found in the different letters ... if this is not what you want, please ignore my answer.

Thank you and sry that I took your time.


Nikolino
I know I don't know anything (Socrates)

[#19]

@killerBee615 

Do you mean like this?

[#20]

@Sergei Baklan yes exactly, how did you add the numbers 22, 33 and 100