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)))
Hello JMB17,
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!
=SUBTOTAL(3,'Raw Data'!$F$2:$F$150)
should do the trick.
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.