Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Copilot for Microsoft 365 Tech Accelerator

Feb 28 2024 07:00 AM - Feb 29 2024 11:00 AM (PST)

Microsoft Tech Community

SOLVED
Home
#
Problem with SUMPRODUCT(SUBTOTAL(3,OFFSET) Formula

- Home
- Microsoft 365
- Excel
- Re: Problem with SUMPRODUCT(SUBTOTAL(3,OFFSET) Formula

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 04 2020 01:32 PM - edited Aug 04 2020 01:33 PM

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

Labels:

14 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 04 2020 02:37 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 04 2020 02:46 PM - edited Aug 04 2020 02:58 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 04 2020 03:10 PM

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.

best response confirmed by
dev1050* (Copper Contributor)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 04 2020 07:27 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 05 2020 11:39 AM - edited Aug 05 2020 11:50 AM

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 05 2020 11:54 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 05 2020 12:08 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 05 2020 12:14 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 05 2020 12:24 PM

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.

=SUBTOTAL(3,'Raw Data'!$F$2:$F$150)

should do the trick.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 05 2020 12:27 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 05 2020 12:28 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 05 2020 07:40 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 06 2020 06:42 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

1 best response

Accepted Solutions

best response confirmed by
dev1050* (Copper Contributor)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 04 2020 07:27 PM

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