Forum Discussion

dev1050's avatar
dev1050
Copper Contributor
Aug 04, 2020
Solved

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 ...
BI & Data Analysis
Formulas and Functions
  • JMB17's avatar
    JMB17
    Aug 05, 2020
    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)))
SergeiBaklan's avatar
SergeiBaklan
MVP
Aug 04, 2020

dev1050 

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.

Resources