08-04-2020 01:32 PM - edited 08-04-2020 01:33 PM
08-04-2020 01:32 PM - edited 08-04-2020 01:33 PM
I am analyzing a lot of Likert-scale data in excel. One tab (labeled "Raw Data") is where the data is input and can be filtered. Another tab (labeled "Likert Analysis") is where I have formulas for analyzing the data on the Raw Data tab. I am attempting to use the formula below to count the number of "5s" in the Raw Data tab's data set for the filtered data only (so, sort of like a COUNTIF for visible data):
=SUMPRODUCT(SUBTOTAL(3,OFFSET('Raw Data'!F2:F150,ROW('Raw Data'!F2:F150)-ROW('Raw Data'!F2:F150),0)),'Raw Data'!F2:F150=5)+0
So far, I only get a "0" in the cell with this formula. I attempted to modify the formula to work for a data set that is on another sheet than the one where this formula is being input, and I may have done something wrong. Any suggestions on how to correctly type the formula or why I am only returning a "0" from the formula?
08-04-2020 02:37 PM
I'm not sure what you'd like to calculate. SUBTOTAL(3,OFFSET returns an array of 149 elements size each of which is equal to number of not empty cells in F2:F150. Next part returns 149 TRUE or FALSE which SUMPRODUCT is ignored, as well as texts, thus result always will be zero. You shall convert second part to numbers as
=SUMPRODUCT( SUBTOTAL(3,OFFSET('Raw Data'!F2:F150,ROW('Raw Data'!F2:F150)-ROW('Raw Data'!F2:F150),0)), ('Raw Data'!F2:F150=5)+0)
when sometimes it'll be non-zero result. Other words, move +0 to the second part of SUMPRODUCT.
08-04-2020 02:46 PM - edited 08-04-2020 02:58 PM
@dev1050 I'm not sure where you got that formula and why you are using it instead of a COUNTIF(). I also assume by "5s" you really mean "5"s (i.e. the number 5 not the text '5s'). I'm also going to guess the original formula you found had some '$' in the cell range references. Just curious why this doesn't work:
BTW, I don't know what is special about '5' if anything but you might want to set a place in your workbook for constants like that and refer to it. If it is a constant maybe define it in the Name Manager. If it is something you will change often put it on the sheet (e.g. N1="This constant ..." and then M1=5 and then put $M$1 in your formula). Or better yet do both and in Name Manager define "Const_Name" => $M$1 and then use that Name in the formula(s).
EDIT: @Sergei Baklan would you agree the original formula 'corrected' in the way you did, could be equally written as:
=COUNTIF('Raw Data'!$F$2:$F$150,5)*COUNTA('Raw Data'!$F$2:$F$150)
08-04-2020 03:10 PM
I simply don't understand what we calculate here, thus can't agree or disagree. The obvious thing was to convert logical values to numbers. With that entire formula always returns COUNTA in F2:F150 or zero, depends on magic 5 is less than size of the range or not. Most probably not what expected.
I have no idea what is "Likert-scale data" and what we shall to calculate for the analysis. Perhaps you are right.
08-04-2020 07:27 PMSolution
08-05-2020 11:39 AM - edited 08-05-2020 11:50 AM
Thank you for your responses. You are correct in stating that I am searching for the number 5 in a given list where the range is 1 through 5 or blank (if the respondent did not answer). As far as using a COUNTIF, I receive all counts of cells with a 5, even when the list is filtered (e.g., by program name).
The Raw Data tab is set up with the following columns from left to right: Program Name, Location, Region, Date, Respondent number (ID), Q1, Q2, Q3, Q4, Q5. Columns F-J are Q1-Q5 (Question 1, Questions 2, etc.) The rows are individual respondents. So, each cell beneath Q1-Q5 will be populated with either a 1, 2, 3, 4, or 5 depending on the respondents answer (or left blank if the respondent didn't answer). These values are in accordance to a Likert scale (1-strongly disagree, 2-disagree, 3-neutral, 4-agree, 5-strongly agree) given these are the results of a survey.
My goal is to see how many respondents answered "5" for Q1. I will also do this for how many 4s, 3s, 2s, and 1s in order to calculate statistics on responses (i.e., I can see that there 45 respondents who put "5" for Q1, 52 respondents put a "4" for Q1, and 48 respondents put a "5" for Q2). I attached a picture of what I am referring to. My reason for attempting the formula provided was to be able to have the formula return only what data is currently visible as I filter the data based of program name, region, etc. Currently, the COUNTIF formula returns all that is in the Raw Data sheet, regardless of filtering because is counts all cells with the value, even if "hidden" or "not visible". After some research, I found this site https://www.extendoffice.com/documents/excel/2519-excel-countif-filtered-data.html explaining how to use SUMPRODUCT to get the information I want.
I hope this helps!
08-05-2020 11:54 AM
I apologize for not attempting your formula prior to my previous reply but YES! This is EXACTLY what I was needing!! THANK YOU!
08-05-2020 12:14 PM
In attempts to understand the formula more, could I ask for an explanation of the formula's parts? I am hoping I can understand it and manipulate it for other needs (e.g., I need to know the total number of surveys. So, I hope I can I change it to just return cells that have anything in them from the filtered data on column E [Respondent ID]). I figured out how to manipulate the formula to collect the number of responses containing 4, 3, 2, and 1!
08-05-2020 12:24 PM
08-05-2020 12:27 PM
08-05-2020 07:40 PM
I attached a file to try to illustrate how the formula is working. There is a tab with comments regarding how the functions are being used and also a tab with an example table that hopefully provides some useful illustration.
Also, here is a link to a site that includes additional explanation regarding the use of sumproduct in multiple condition tests that might also be helpful.