Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to use SUMIFS and INDIRECT Functions to perform a dynamic Total Sales.
SUMIFS is a native Statistical function that adds the cells specified by a given criteria
SUMIFS Syntax =SUMIFS(sum_range, criteria_range1, criteria1, ..)
INDIRECT is a native elegant Lookup function. It delivers the reference specified by a text string
INDIRECT Syntax =INDIRECT(ref_text, [a1])
For this tutorial, we have transaction data for Property Sales for the month of January to June. Note, the data in each sheet has the same structure.
In the Report worksheet, we have List Data Validation in cells E12:H12 for Month, Sales Manager, Property Type and Estate Type respectively
In cell E13, execute the formula: =SUMIFS(INDIRECT(E12&”!C:C”),INDIRECT(E12&”!G:G”),F12,INDIRECT(E12&”!D:D”),G12,INDIRECT(E12&”!F:F”),H12)
INDIRECT(E12&”!C:C”) – the sum_range argument input values
INDIRECT(E12&”!G:G”),F12 – criteria_range1 and criteria1 arguments input values
INDIRECT(E12&”!D:D”),G12 – criteria_range2 and criteria2 arguments input values
INDIRECT(E12&”!F:F”),H12 – criteria_range3 and criteria3 arguments input values
Total Sales for Phoebe Harding Sales Manager, Detached Property Type, Freehold Estate Type in the Month of April is ₦955,000. Job done!
Amazing excel post