IF formula and AND
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
Best Answers
-
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
-
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
Answers
-
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
-
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
-
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
-
Thanks Kelly and this is quite helpful. Will let you know if any other questions. Appreciate your help.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives