Skip to main content

Summing and Counting Criteria in filtered data (Using SUMPRODUCT and SUBTOTAL Functions)





Hi One and all,

 This article will demonstrate that how can we apply further criterias on Filtered data using formulas and get to the conclusions. So here we go:-

So First lets concentrate on Count Part.. The formula I used is..
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B1,ROW(B2:B16)-1,)),--(B2:B16="A"),--(C2:C16>100))
First lets discuss the bold portion only i.e.
SUBTOTAL(3,OFFSET(B1,ROW(B2:B16)-1,))
Which extends to
SUBTOTAL(3,OFFSET(B1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},))
which further extends to
SUBTOTAL(3,{B2;B3;B4;B5;B6;B7;B8;B9;B10;B11;B12;B13;B14;B15,B16})



Now here comes the tricky portion where CountA(3) and Subtotal performs simultaneously.
CountA(3) will count NON Blank cells and show 1 for each non blank cell in the array.
AND
Subtotal will Show Zero for the corresponding hidden cell as it does normally.

Subtotal Function overrides the array given by CountA function (as all cells in the Array are non blanks) and shows Zeros for cells which are hidden.the resultant array would be like this.
{0;1;1;0;1;1;1;1;1;0;1;1;1;1;1}
Here is the Tabular Illustration:-
corresponding array element
Hidden A2 0
A3 1
A4 1
Hidden A5 0
A6 1
A7 1
A8 1
A9 1
A10 1
Hidden A11 0
A12 1
A13 1
A15 1
A16 1
This was the tricky part which I hope I have been able to explain well..
Further, --(B2:B16="A") AND --(C2:C16>100) filters the result more with their corresponding conditional arrays and
=SUMPRODUCT({0;1;1;0;1;1;1;1;1;0;1;1;1;1;1},--(B2:B16="A"),--(C2:C16>100))
will return the answer 2 which is evident from the above filtered data.
For sum purposes, we have to add one more range..
--C2:C16
So that sumproduct will return 600 (250+350)
Use FORMULA AUDITING if you wanna understand it more ….

I hope, I have been able to explain it well….
But if you want me to improve the explanation or have any other query then please comment.
I will act upon for sure.
Regards,
Vikas Gautam

File can be downloaded from here..
Sumproduct with subtotal.xlsx
Labels:

Popular posts from this blog

Automation:- Sending Invitation to Meeting Using Excel VBA

Hello Everyone, In one of the previous post, I wrote about automating Sending Emails using Excel VBA. This time I have come up with a pretty similar code. The code below sends Outlook Meeting Invitations to recipients on one click. here is the Code:- Sub Send_Invite_Auto() Dim olApp As Outlook.Application Dim olApt As AppointmentItem Set olApp = New Outlook.Application 'Creating Outlook Session Set olApt = olApp.CreateItem(olAppointmentItem) 'Creating an Appointment With olApt .Subject = "Enter the subject here." 'Subject .Start = DateAdd("d", 5, Now) 'Enter Date + Time here. .Recipients.Add ("example@gmail.com") 'Recipient Name, Alias, or any other Attribute. .MeetingStatus = olMeeting 'olAppointmentItem with Meeting status olMeeting 'becom
10 comments
Read more

Highlighting Duplicates across multiple sheets

Hi One and All, This time I have come up with some conditional formatting stuff. The aim is to highlight the duplicates across multiple sheets and with in the sheet as well. Assumptions:- 1. I am assuming that sheet names goes on like sheet1, sheet2, sheet3.... 2. The Target Column No. is same in all the sheets. I mean, as in the attached example, its Column A which is being targeted in both sheets.
2 comments
Read more

Making Password Protected PDFs using Excel Vba and PDFtk Tool

Hi Everyone, This time, I have come up a VBA Code to generate Password protected PDFs using Excel. Actually, Excel Vba has .ExportAsFixedFormat Method to generate PDFs but this hasn't any Parameter which takes password to protect the PDFs. So I have used PDFtk Tool which provide Command Line Interface to make PDFs protected using Password. Actually, You can do various things using PDFtk Tool command line varying from creating, merging, Protecting and many other. So Download the PDFtk Tool from the following link:- Download PDFtk Tookit Here are the Steps:- 1. Install the PDFtk Toolkit. 2. Use the following code to Print or Export the Activesheet with a password.
8 comments
Read more