SUMPRODUCT Function To Find the Sum based on Multiple Criteria

SUMPRODUCT Function To Find the Sum based on Multiple Criteria

SUMPRODUCT Returns the sum of the products of corresponding ranges or arrays. The SUMPRODUCT is a multipurpose function used to find the count and sum based on multiple criteria.

Sumproduct is a Most Powerful Function that can did the Job of the functions like Sum, SumIf, Sumifs, Count, Countif and Countifs.

Syntax:

=SUMPRODUCT (Array1, Array2, Array3, ...)

Example-I : Working with Multiple Criteria:

Let us consider the following example where we have the data by Months and Quarters.

Here if you want to find Total sum for 'FY' ,which means sum of All months data(excluding Quarterly Sums) , which can be done by using the following Sumproduct formula with multiple criteria :

=SUMPRODUCT((($A$1:$P$1<>"Q1")*($A$1:$P$1<>"Q2")*($A$1:$P$1<>"Q3")*($A$1:$P$1<>"Q4"))*(A2:P2))

---------------------------------------------------------------------------------------------------------------------------------

Example-II:

Formula Usage to find Sum:

Note :
We can use this logic in different scenarios based on our requirement.

Thanks,
TAMATAM
     [ BI-Reporting Analyst ]

How to get filtered Pivot Data from an Input File in to a Target Template File using Excel VBA Macro

Excel VBA Macro to get filtered Pivot Data from an .xlsb Input File in to our Target Template File

'This Macro will open the target Input File and then go to the target Pivot Table in a desired sheet and apply the desired Filters and extracts the data(Show details data/Get Pivot data from Grand Totals) and then copy and paste that extracted data in to our Report Template.

Sub GetFilteredPivotData()

Dim SourcePath As String

Dim SrcFileName As String

   

Dim SourceFile As String

Dim TargetFile As Workbook

Dim MyRange As Long

Dim TargetRange As Long

Dim CurQtr As String

Dim PVT_GrandTotal As Range

Dim X As Integer

Dim Y As Integer
Dim Response As Integer

SourcePath = "C:\Tamatam\Input\"

SourceFile = "Reddys_Detailed_Sales_Report.xlsb"

ThisWorkbook.Sheets("LastWeekSales").Activate

MyRange = ActiveSheet.UsedRange.Rows.Count

Range("$A$2:$BF$" & MyRange).Select

Selection.Clear

Set TargetFile = Workbooks.Open(SourcePath & SourceFile)

TargetFile.Activate

Worksheets("WeeklySales").Select

ActiveSheet.PivotTables("PivotTable5").PivotFields("Forecast_State").CurrentPage = "Committed"

' The Following Code will Unfilter the Blanks in PivotFilter:

' For x = 1 To ActiveSheet.PivotTables("PivotTable5").PivotFields("XYZ").PivotItems.Count

' If InStr(1, ActiveSheet.PivotTables("PivotTable5").PivotFields("XYZ").PivotItems(x).Value, "") > 0 Then

' ActiveSheet.PivotTables("PivotTable5").PivotFields("XYZ").PivotItems(x).Visible = False

' End If

' Next

QTR:
'Passing the desired Quarter value through input box to Pivotfilter Field.    
CurQtr = InputBox("Enter Current Quater as : FY2014-Q4 ", "Current Quarter")

If CurQtr = "" Then Exit Sub 'If you Cancel the Inputbox then Macro will exit

If (CurQtr = "FY2014-Q4" Or CurQtr = "FY2015-Q1" Or CurQtr = "FY2015-Q2" Or CurQtr = "FY2015-Q3" Or CurQtr = "FY2015-Q4") Then

' The Following Code will loop through Each item in a Pivot Field and Filter data by a value equals to the CurQtr Input.

For X = 1 To ActiveSheet.PivotTables("PivotTable5").PivotFields("Quarter").PivotItems.Count

If InStr(1, ActiveSheet.PivotTables("PivotTable5").PivotFields("Quarter").PivotItems(X).Value, CurQtr) > 0 Then
ActiveSheet.PivotTables("PivotTable5").PivotFields("Quarter").PivotItems(X).Visible = True
Else
ActiveSheet.PivotTables("PivotTable5").PivotFields("Quarter").PivotItems(X).Visible = False
End If

Next
   
Else
    
Response = MsgBox("Please give FY0000-Q0 in correct Format !!", vbRetryCancel, "Wrong Input !!")
    
    If Response = vbRetry Then
    GoTo QTR:
    Else
    Exit Sub
    End If
    

End If

Set PVT_GrandTotal = ActiveSheet.Range("A13").PivotTable.GetPivotData("ExpectedSales")

'This code line will extract the Details for the filtered Quarter data from the Pivot.

PVT_GrandTotal.ShowDetail = True

TargetRange = ActiveSheet.UsedRange.Rows.Count

Range("$A$2:$BF$" & TargetRange).Select

Selection.Copy

ThisWorkbook.Activate

ActiveWorkbook.Sheets("LastWeekSales").Range("$A$2").Select

ActiveSheet.Paste

Application.CutCopyMode = False

TargetFile.Close Savechanges:=True

ActiveSheet.Range("$A$2").Select

ThisWorkbook.Save

End Sub