Problem with Countif formula

[xafrodith]

Hi Everyone,

I am trying to created a tool to process all the answers from the surveys we conduct, we have multiple choices that comes as multiple answers in a cell separated by ";" symbol.
I need a formula that will count how many times a specific answer appears in a cell , but also will count any value different that the specific answer.

I hope this makes sense...

Thank you!

 

[p45cal]

In the attached file there are 3 formulae in cells B2,D2 & F2 of the Final_Results sheet.
The results spill down. If there are other answers apart from the list of possible answers referred to in the formula, their count is tacked onto the bottom of the results and you can label them Other.
The result table can't be a proper Excel table since these dynamic array formulae don't work well with tables.
There are hints as you enter a new fomula as to what goes where:



This formula is a lambda formula and you can see it in Excel's Name Manager. It depends on a second lambda formula (myResults), which formula you can, if you wanted, use on a sheet yourself:
=myResults(Table1[Given the option, which language would you feel most comfortable being trained in?])
which will list only the actual results given in the survey and their counts as a 2-column array:


See how Greek is included, and Arabic doesn't feature at all.

If this works for you, we can go into more detail/possibilities.

 

[p45cal]

Oh groan, cross posted without links:

Problem with Countif formula

Hi Everyone, I am trying to created a tool to process all the answers from the surveys we conduct, we have multiple choices that comes as multiple answers in a cell separated by ";" symbol. I need a formula that will count how many times a specific answer appears in a cell , but also will...

www.excelforum.com

@xafrodith , for your information, you should always provide links to your cross posts.
This is a requirement, not just a request.
If you have cross posted at other places, please add links to them too.
Why? Have a read of https://excelguru.ca/a-message-to-forum-cross-posters/


psst… it's also polite to give feedback (eg. at ExcelForum) when it's obvious that several people have made significant effort to help you, we're not AI chatbots, we're human (it'll also make it much more likely people will help you again!).

 

[p45cal]

Oh groan, cross posted without links:

…and another one:

Countifs multiple entries in a cell

Hi Everyone, I am trying to created a tool to process all the answers from the surveys we conduct, we have multiple choices that comes as multiple answers in a cell separated by ":". I need a formula that will count how many times a specific answer appears in a cell , but also will count any...

www.mrexcel.com