I want to sum up the number of times a string field has values

justinjp · April 24, 2023, 8:28pm

I have survey data I ingested into QS and one of the fields has text if they selected it or is blank if they didnt. When I pivot, it shows the count for all 295 fields but i just want the number of fields that are not blank to show Eg. Colum has “Text” 165 out of 295 times. What is the easiest way to go about this?

justinjp · April 24, 2023, 9:56pm

ifelse(
locate({Q11_1},“text displayed entered here”.”) > 0,
“1”,
NULL)

Jesse · April 24, 2023, 10:21pm

Hi @justinjp maybe you can try something like this:

ifelse({Q11_1}=“”, NULL, {Q11_1})

This is assuming there is an ‘empty’ string in the cell. If there is a space then would want to put a space in between those two quotations.

justinjp · April 25, 2023, 1:51am

@Jesse that didnt work - kept getting syntax error

Jesse · April 25, 2023, 6:46pm

@justinjp You are getting a syntax error when trying to save the calculated field? Can you send a screenshot of your formula?

Kristin · May 7, 2023, 12:29am

Hi @justinjp. Checking in. Hope you can send the screenshot that Jesse requested, We’d still like to help, so please send it over if you can. If we do not hear back in the next 5 days, we will archive the question

royyung · May 9, 2023, 10:40pm

The value returned of the ifElse condition must be the same data type.

I’ve tried below scenario, it works. Can share your screenshot?
ifelse(locate({Order ID}, ‘EMEA’)>0, “1”, NULL)