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)
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.