SUMIFS Function With 4 INDIRECT Function To Deliver Dynamic Total Sales

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!

Posted in Blog.

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *