Countif returning 1 count while the cell contains 2

Question

I'm using google forms a lot and also with tickboxes. From this form a spreadsheet is generated and has for example a cell with the following values:

Green 1x, Blue 1x, Red 1x

Now I would like to count the 1x in a cell. Over multiple cells the following countif would work:

=countif(A1:A100; "*1x*") 

However this will return a 1 count for the above data while in fact I want it to return 3!

I have looked at splitting the cells for every entry and due to the shear amount of data this makes my other data hard(er) to handle.

Is there a way to set-up the countif formula in a way that it counts also within a cell?

Edit: After some more investigating I have found a way to work around without using countif.

(LEN(A1)-LEN(substitute(A1;"1x";"")))/2)

This returns the amount of 1x within a certain cell. I think I can clear it up by using a trim function to remove the /2 part. However I'm still think about how to implement that one.

Answer 1

To count the number of '1x' in a cell assuming the string is in A1 try:

=COLUMNS( SPLIT( A1 , "1x" ) )

Answer 2

If you do a Replace of 1x by 1x the count (of all within the range selected, whether or not one to a cell) should display on screen.

Answer 3

After some more investigating I have found a way to work around without using countif.

(LEN(A1)-LEN(substitute(A1;"1x";"")))/2)

What it does is that it now returns the times a space is used and subsequently I have substituted 1x by a space. However the way my form is set-up it will also count the spaces added after every "1x". That's why I have added the /2.

A work around for this is using the TRIM function.

Answer 4

EXAMPLE: COUNTA(A2:A100, B2:B100)