Separating numbers in an excel cell

Visitor

Hi, do you know of any excel formula to count the number of times each number occurs in a particular row? I mean I will have a separate column for each count of the numbers on the right.  

 

fathy965_0-1645184640726.png

 

2 Replies

@fathy965 

=COUNTIF($B$1:$AG$1,A2)

Is this what you are looking for?

Hi @fathy965 

Another interpretation of your need is like

bosinander_0-1645197336098.png

where row 2, BAJOGA's number 1 has been seen three times as well as also number 2.

If so;

=LET(
     range;  $B2:$E2;
     number; H$1;
numbers;     TEXTJOIN(" ";1;range);
html;        "<tr><td>" & SUBSTITUTE(numbers;" ";"</td><td>") & "</td></tr>";
array;       TRANSPOSE(FILTERXML(html;"//td"));
output;      SUM(--(array=number));
output
)

You can change the last output (row to eg html and see how each step transforms data in the final output.