For example, there is a table with two columns:

VRT | one
VRT | one
VRT | 2
DMT | one
DMT | five
TIM | 2
TIM | four
TIM | five
VRT | 3
DMT | one
DMT | 2
TIM | 2

I need to count how many digits for each block. For example, how many 1 and 2 are in VRT, how many are 5 in DMT, etc.

The result should be like this:
VRT1 = 2 pcs
VRT2 = 1 pcs
VRT3 = 1 pc
DMT1 = 2 pieces
DMT5 = 1 pc
DMT2 = 1 pc
TIM2 = 2 pcs
TIM4 = 1 pc
TIM5 = 1 pc

How to do it?

  • can better use the pivot table? - Batanichek
  • I need to calculate each separately, for example, how many odnnerok the VRT and so on for each I will do. - Patrick

2 answers 2

In my opinion it is better to use pivot tables for such tasks.

If you want for a particular pair, then just like this ($ A $ 1: $ A $ 12 and $ B $ 1: $ B $ 12 is a column of values):

=小效衼孝袝小袥袠袦袧($A$1:$A$12;"VRT";$B$1:$B$12;1)

Here is an option through 小效衼孝袝小袥袠袦袧 for all pairs

1) we make a table of unique pairs - copy the entire table - delete duplicates (Data-> Delete duplicate)

2) Make 小效衼孝袝小袥袠袦袧 for each unique pair: =小效衼孝袝小袥袠袦袧($A$1:$A$12;H1;$B$1:$B$12;I1)

enter image description here

  • wow, it worked out! thank! - Patrick

Many people use Excel 2003 , where there are no COUNTILES .

Option to replace the missing function:

 =小校袦袦袩袪袨袠袟袙(--($A$2:$A$13="VRT");--($B$2:$B$13=1))

With add. the column where the data is concatenated ( = A2 & B2 ):

 =小效衼孝袝小袥袠($C$2:$C$13;C2)

With the presence of concatenated data, you can select a unique formula:

 =袠袧袛袝袣小($C$2:$C$13;袦袠袧(袝小袥袠(小效衼孝袝小袥袠($E$3:E3;$C$2:$C$13)=0;小孝袪袨袣袗($C$2:$C$13)-1)))

The array formula is entered by simultaneously pressing Ctrl + Shift + Enter

Without additional column is also possible, but the formula is more complicated.

And if unique are selected, then it鈥檚 easy to count. enter image description here

Yellow range: below the unique will repeat the first unique value. To eliminate the formula you need to complicate.