Problem with SUMPRODUCT(SUBTOTAL(3,OFFSET) Formula

[dev1050]

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 formulas for analyzing the data on the Raw Data tab. I am attempting to use the formula below to count the number of "5s" in the Raw Data tab's data set for the filtered data only (so, sort of like a COUNTIF for visible data):

=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

So far, I only get a "0" in the cell with this formula. I attempted to modify the formula to work for a data set that is on another sheet than the one where this formula is being input, and I may have done something wrong. Any suggestions on how to correctly type the formula or why I am only returning a "0" from the formula?

Thank you!!

[#2]

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

[#3]

@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: @Sergei Baklan  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)

[#4]

@mtarler 

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.

[#5 · Accepted solution]

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

[#6]

Hi @mtarler, @Sergei Baklan, 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!

[#7]

@JMB17 

I apologize for not attempting your formula prior to my previous reply but YES! This is EXACTLY what I was needing!! THANK YOU!

[#8]

No worries. I'm glad that worked for you.

[#9]

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!

[#10]

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

[#11]

I can try to explain better how the formula works, but it probably won't be until later this evening.

[#12]

Yes, @JMB17 !! This did work! Thank you!!

[#13]

@dev1050 

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.

http://xldynamic.com/source/xld.SUMPRODUCT.html

[#14]

@JMB17,

Thank you so much!!

[#15]

You're quite welcome!