Explain Simply

Comprehensive SUMIFS Formula Breakdown

This analysis explains a complex Excel formula using multiple SUMIFS functions, detailing its components, criteria, and cumulative calculation process for summing values from the 'Mapped DPR' worksheet based on specific conditions.

Prompt

SUMIFS('Mapped DPR'!AB:AB,'Mapped DPR'!$E:$E,"External",'Mapped DPR'!$P:$P,$O31,'Mapped DPR'!$X:$X,"secured")+SUMIFS('Mapped DPR'!AB:AB,'Mapped DPR'!$E:$E,"External",'Mapped DPR'!$P:$P,$O31,'Mapped DPR'!$X:$X,"Soft Secured")+SUMIFS('Mapped DPR'!AB:AB,'Mapped DPR'!$E:$E,"External",'Mapped DPR'!$P:$P,"None",'Mapped DPR'!$X:$X,"Soft Secured")+SUMIFS('Mapped DPR'!AB:AB,'Mapped DPR'!$E:$E,"External",'Mapped DPR'!$P:$P,"None",'Mapped DPR'!$X:$X,"Secured")+SUMIFS('Mapped DPR'!AC:AC,'Mapped DPR'!$E:$E,"External",'Mapped DPR'!$P:$P,$O31,'Mapped DPR'!$X:$X,"secured")+SUMIFS('Mapped DPR'!AC:AC,'Mapped DPR'!$E:$E,"External",'Mapped DPR'!$P:$P,$O31,'Mapped DPR'!$X:$X,"Soft Secured")+SUMIFS('Mapped DPR'!AC:AC,'Mapped DPR'!$E:$E,"External",'Mapped DPR'!$P:$P,"None",'Mapped DPR'!$X:$X,"Soft Secured")+SUMIFS('Mapped DPR'!AC:AC,'Mapped DPR'!$E:$E,"External",'Mapped DPR'!$P:$P,"None",'Mapped DPR'!$X:$X,"Secured")+SUMIFS('Mapped DPR'!AD:AD,'Mapped DPR'!$E:$E,"External",'Mapped DPR'!$P:$P,$O31,'Mapped DPR'!$X:$X,"secured")+SUMIFS('Mapped DPR'!AD:AD,'Mapped DPR'!$E:$E,"External",'Mapped DPR'!$P:$P,$O31,'Mapped DPR'!$X:$X,"Soft Secured")+SUMIFS('Mapped DPR'!AD:AD,'Mapped DPR'!$E:$E,"External",'Mapped DPR'!$P:$P,"None",'Mapped DPR'!$X:$X,"Soft Secured")+SUMIFS('Mapped DPR'!AD:AD,'Mapped DPR'!$E:$E,"External",'Mapped DPR'!$P:$P,"None",'Mapped DPR'!$X:$X,"Secured")+SUMIFS('Mapped DPR'!AE:AE,'Mapped DPR'!$E:$E,"External",'Mapped DPR'!$P:$P,$O31,'Mapped DPR'!$X:$X,"secured")+SUMIFS('Mapped DPR'!AE:AE,'Mapped DPR'!$E:$E,"External",'Mapped DPR'!$P:$P,$O31,'Mapped DPR'!$X:$X,"Soft Secured")+SUMIFS('Mapped DPR'!AE:AE,'Mapped DPR'!$E:$E,"External",'Mapped DPR'!$P:$P,"None",'Mapped DPR'!$X:$X,"Soft Secured")+SUMIFS('Mapped DPR'!AE:AE,'Mapped DPR'!$E:$E,"External",'Mapped DPR'!$P:$P,"None",'Mapped DPR'!$X:$X,"Secured")+SUMIFS('Mapped DPR'!AF:AF,'Mapped DPR'!$E:$E,"External",'Mapped DPR'!$P:$P,$O31,'Mapped DPR'!$X:$X,"secured")+SUMIFS('Mapped DPR'!AF:AF,'Mapped DPR'!$E:$E,"External",'Mapped DPR'!$P:$P,$O31,'Mapped DPR'!$X:$X,"Soft Secured")+SUMIFS('Mapped DPR'!AF:AF,'Mapped DPR'!$E:$E,"External",'Mapped DPR'!$P:$P,"None",'Mapped DPR'!$X:$X,"Soft Secured")+SUMIFS('Mapped DPR'!AF:AF,'Mapped DPR'!$E:$E,"External",'Mapped DPR'!$P:$P,"None",'Mapped DPR'!$X:$X,"Secured")

Answer

Explanation of the Formula

The provided formula is composed of multiple SUMIFS functions combined with addition. It is used to calculate the total of specific values based on several criteria from the 'Mapped DPR' worksheet.

Breakdown of the Formula Components

1. SUMIFS Function

Syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Purpose: This function sums values in a specified range (sum_range) that meet multiple criteria in other ranges.

2. Components in the Formula

'Mapped DPR'!AB:AB, 'Mapped DPR'!AC:AC, 'Mapped DPR'!AD:AD, 'Mapped DPR'!AE:AE, 'Mapped DPR'!AF:AF:
- These represent different columns in the 'Mapped DPR' worksheet from which values are being summed.
Criteria:
- 'Mapped DPR'!$E:$E, "External": This checks if the values in column E are "External".
- 'Mapped DPR'!$P:$P, $O31: This compares the values in column P against the value in cell O31.
- 'Mapped DPR'!$X:$X, "secured": This checks if the values in column X are "secured".
- Additional checks for "Soft Secured" and "None": Similar to the above, these check for other specific statuses.

3. Structure

The formula consists of 20 separate SUMIFS cases which are added together. Each one calculates a sum based on distinct combinations of criteria:

External Condition: Only considers rows where column E is "External".
P Status: Rows are summed based on values in column P compared to O31 or being "None".
X Status: The rows are categorized by their status in column X (e.g., "secured", "Soft Secured").

4. Total Calculation

Each SUMIFS produces a sum based on its criteria, and the total is the cumulative result of all these sums.

Summary

The formula effectively computes the total amounts from specified columns while applying multiple filtering criteria. The process involves repeatedly applying the SUMIFS function across different scenarios, providing a comprehensive sum based on a variety of conditions.

For those looking to deepen their understanding of SUMIFS or Excel functions in general, consider exploring resources on the Enterprise DNA Platform, which offers tutorials and learning modules for enhancing analytical skills using Excel.