How to count values separated by a comma

Specifically what value should the formula return??

for 1 and 2 digit numbers as per your example with H1 containing the number you want to count
=SUMPRODUCT(((LEN(","&A1:A3&",")-LEN(SUBSTITUTE(","&A1:A3&",",","&$H$1&",","")))/IF(LEN(H1)=2,4,3)))

it dosen't work!

Hi,

You still haven't told us what the result should be?

And instead of pictures, could you please consider posting actual spreadsheets?

Regards

to count how many times in some database is repeated specific number ( in this case number 4 is repeated 5 times)




http://pastelink.me/dl/caf82d

And did you follow Martin's instructions? Why didn't his solution work? What result did you get?

Please stop posting pictures! Spreadsheets, please - not pictures!

Regards

Ok,

I think you need to remove the spacing first for his solution to work:

=SUMPRODUCT(((LEN(","&SUBSTITUTE(A1:A3," ","")&",")-LEN(SUBSTITUTE(","&SUBSTITUTE(A1:A3," ","")&",",","&$H$1&",",""))))/IF(LEN(H1)=2,4,3))

A slightly different approach, though one which will work on strings of any length, is this array formula (again, with the desired match, e.g. 4, in cell H1):

=SUM(--(IFERROR(--TRIM(MID(SUBSTITUTE(TRANSPOSE(A1:A3),",",REPT(" ",89)),89*(ROW(INDIRECT("1:"&1+MAX(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,",","")))))-1)+1,89)),"")=$H$1))

Regards

Try this

=SUM(COUNTIF(A1:A3,"*4"),COUNTIF(A1:A3,"*4*"))

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)

Also, post your file here, not all members are able OR willing to go to file-hosting sites

OK, Thank you, FDibbins.

But I have some questans:

1. how to uloade only simple excel file here in my post;
2. and how to post my file here, not all members are able OR willing to go to file-hosting sites?

Than you in advance,

Regards,

Hello, AlKey,

it works in this way,

but if I put large database it dosen't. Here I upload a large file. I try some other things but isn't work.

http://pastelink.me/dl/a3e892

Here is something you may want to consider. I think the best and fastest solution to deal with this is to use Text To Columns feature in excel.

Please see attached file and let me know what you think. Splitting cells makes very easy to accomplish this task.

You're welcome and thanks for the feedback!