IF formula and AND

Debbie Marcusson

Hello I am a novice on Smartsheet as well as formulas. I have created a sheet that tally's the results of survey. I created an IF statement to calculate the answers =COUNTIF({CCA Training Survey Range 5}, "strongly agree") and the next column is =COUNTIF({CCA Training Survey Range 5}, "agree"), etc. to the strongly disagree. I can get a running total and percentage but now I need to pull out the results of survey by month. So, I need to know the results of survey for month of January, February, March, etc. as well as the running total which I already have. I know you can do an AND statement for dates but not sure if you can combine?; hopefully this question makes sense.

Thanks, Debbie

Kelly Moore

Hey @Debbie Marcusson

Yes, you can have multiple criteria using the COUNTIFS (plural) function. The syntax is the same that you're already using

=COUNTIFS(range1, criteria1, range2, criteria2, range3, criteria3......) with as few or as many ranges-criteria that you desire. You must always add the terms as a range-criteria pair. The different terms within COUNTIFS are AND criteria, that is the Count will be based on criteria1 AND criteria2 AND... all being true.

Designating the month depends on how your data is being gathered. Are you rolling this into a summary sheet so that you have the months 1-12 already designated in your target sheet? If you can help me understand your data, I will be happy to help you build your formulas.

Kelly

Kelly Moore

Hey Debbie

Thinking about how to layout your data in the summary sheet - off the top of my head (not knowing your source data) I would have these columns

Month Name, Month Number (this could be hidden), Year?, Strongly Agree, Agree, Disagree, Strongly Disagree, etc

I assumed you were probably using the Created date in your source sheet? Whatever the date column is, you can easily reference the Month from that. Again, not knowing your source data, I would think you could have a summary sheet that is easy to maintain year on year (always important to me), particularly if you leverage summary sheet formulas on your summary data. I often with either move/copy rows to a single archive sheet so I can easily compile year on year comparisons of data - depending on how large the data set is for each year.

If there's anything I can do to help, just shout out to me.

cheers,

Kelly

Kelly Moore

Hey @Debbie Marcusson

Yes, you can have multiple criteria using the COUNTIFS (plural) function. The syntax is the same that you're already using

=COUNTIFS(range1, criteria1, range2, criteria2, range3, criteria3......) with as few or as many ranges-criteria that you desire. You must always add the terms as a range-criteria pair. The different terms within COUNTIFS are AND criteria, that is the Count will be based on criteria1 AND criteria2 AND... all being true.

Designating the month depends on how your data is being gathered. Are you rolling this into a summary sheet so that you have the months 1-12 already designated in your target sheet? If you can help me understand your data, I will be happy to help you build your formulas.

Kelly

Debbie Marcusson

Hi Kelly,

Thanks for the information this makes sense. I would like to roll this up to a summary sheet then will be on a dashboard. Have data for each month as well as accumulative. I have 5 survey questions with answers from Strongly Agree to Strongly disagree. Those answers are then calculated into number. So I have the running number as the survey results all go to same sheet; as this is ongoing customer service survey. With that I would like to see how to take the survey results and break it down into the months. I think what you said about COUNTIFS and the AND formula will work. Well at least that is what I am thinking. LOL

Thanks for your guidance. Debbie

Kelly Moore

Hey Debbie

Thinking about how to layout your data in the summary sheet - off the top of my head (not knowing your source data) I would have these columns

Month Name, Month Number (this could be hidden), Year?, Strongly Agree, Agree, Disagree, Strongly Disagree, etc

I assumed you were probably using the Created date in your source sheet? Whatever the date column is, you can easily reference the Month from that. Again, not knowing your source data, I would think you could have a summary sheet that is easy to maintain year on year (always important to me), particularly if you leverage summary sheet formulas on your summary data. I often with either move/copy rows to a single archive sheet so I can easily compile year on year comparisons of data - depending on how large the data set is for each year.

If there's anything I can do to help, just shout out to me.

cheers,

Kelly

Debbie Marcusson

Thanks Kelly and this is quite helpful. Will let you know if any other questions. Appreciate your help.