Forum Discussion
dev1050
Aug 04, 2020Copper Contributor
Problem with SUMPRODUCT(SUBTOTAL(3,OFFSET) Formula
Hi everyone, 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 ...
- Aug 05, 2020The 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)))
JMB17
Aug 05, 2020Bronze Contributor
No worries. I'm glad that worked for you.
dev1050
Aug 05, 2020Copper Contributor
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!
- JMB17Aug 05, 2020Bronze ContributorI can try to explain better how the formula works, but it probably won't be until later this evening.
- JMB17Aug 05, 2020Bronze ContributorIf you just need the total number of items in the filtered range, then I think
=SUBTOTAL(3,'Raw Data'!$F$2:$F$150)
should do the trick.- JMB17Aug 06, 2020Bronze Contributor
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.