Forum Discussion
Problem with SUMPRODUCT(SUBTOTAL(3,OFFSET) Formula
- The formula can be fixed to not return an error, but it's hard to say if that is what you want as you've not stated what you're trying to calculate. As it stands, it appears the formula will count the number of 5's in Column F and multiply that count by the number of visible rows in the filtered range, which seems odd.
By chance, are you trying to count the number of 5's in Column F that are visible (perhaps some are invisible due to filters applied to other columns)? If so, then I think this is what you want (hit Ctrl+Shift+Enter after copying/typing into the formula bar):
=SUMPRODUCT(--('Raw Data'!$F$2:$F$150=5),SUBTOTAL(3,OFFSET('Raw Data'!$F$2,ROW('Raw Data'!$F$2:$F$150)-ROW('Raw Data'!$F$2),0,1,1)))
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:
=COUNTIF('Raw Data'!$F$2:$F$150,5)
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: SergeiBaklan 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)
Hi mtarler, SergeiBaklan, and JMB17 ,
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!
