Counting frequencies in one row

[letime64]

Hi everybody,

I am looking for a way/formulas/function to solve my problem. I get a database from a questionnaire and one of the questions have multiple answers that you could combine (e.g. a, b, c and d) and as a results I get:

1 - a
2 - a + b + c
3 - b + d
etc.


I would like to know if it's possible to easily count the a, b, c and d separately (giving here: a:2, b:2, c:1 and d:1).

Hope my question is clear enough.

Thanks

[JohnTopley]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[letime64]

Thanks for your reply.

Please find enclosed the example.

I did not make all the counting so the XX should be replaced by the number of times the month appear in the month column.

[bebo021999]

Assuming A1:A4 contain strings

D1 ="a" (or b,c,d,...)

Try:

=SUMPRODUCT(LEN($A$1:$A$4)-LEN(SUBSTITUTE($A$1:$A$4,D1,"")))

[bebo021999]

With your sample,

=SUMPRODUCT(LEN(fromquestionnaire!$B$2:$B$87)-LEN(SUBSTITUTE(fromquestionnaire!$B$2:$B$87,A2,"")))/LEN(A2)

be warned with typo "Decemper" in last rows

[canapone]

Hi All

in B2 to be copied down

=COUNTIF(fromquestionnaire!B:B,"*"&A2&"*")

February should return 72

Regards

[Phuocam]

Or count "a":

=COUNTIF(A1:A4,"*a*")

[letime64]

Thanks for your reply.

I get some different results if I use the SUMPRODUCT or the COUNTIF formula?
Which one is more relevant? It seems that countif is more accurate.

[avk]

Try with in "B2"

Please Login or Register  to view this content.

copy paste down
Note : You need to change month as per specified

[bebo021999]

Woooh ! Great ! It's working, thanks a lot.

Could you explain the logic behind it? I understand the LEN function and the idea of comparing the length of the text but I don't get the use of the other formulas (and how to avoid miscounting of june and july for example with both 4 letters).

Thanks again

Nice to hear it works.
Let say we have string in cell A1 = "zzzzabcyyyyabc" in which we 'd like to count "abc" as twice.
if we use SUBSTITUTE to eliminate "abc" like this: SUBSTITUTE(A1,"abc",""), returns "zzzzyyyy"
LEN(A1)-LEN(SUBSTITUTE(...)) = 14-8=6, with 6 is total lenght of number of "abc"
Now divide to 3 (length of "abc")

[letime64]

Nice to hear it works.
Let say we have string in cell A1 = "zzzzabcyyyyabc" in which we 'd like to count "abc" as twice.
if we use SUBSTITUTE to eliminate "abc" like this: SUBSTITUTE(A1,"abc",""), returns "zzzzyyyy"
LEN(A1)-LEN(SUBSTITUTE(...)) = 14-8=6, with 6 is total lenght of number of "abc"
Now divide to 3 (length of "abc")

Sorry I edited my post as it is not working compared to countif, more easy to use and more acurate. Thank you for your help and the explanation.

[bebo021999]

2 options with COUNTIF and LEN for you!

If A1 like this:

"January-February...", COUNTIF is best choice

but, If A1 like this:

"January-February-January...", LEN(SUBSTITUTE... is acurate.

[letime64]

2 options with COUNTIF and LEN for you!

If A1 like this:

"January-February...", COUNTIF is best choice

but, If A1 like this:

"January-February-January...", LEN(SUBSTITUTE... is acurate.

Thanks, I am in the first case so i will go for the countif but i keep in mind the LEN solution.