Forum Discussion
Solved
Find the total amount value based on different category Products for a specific salesman
Hey there. I am attaching a workbook which is having three different sheets. 1. Raw Data 2. Employee details 3. ST Products Here I am trying to find the total ST ACT of a particular salesman bas...
An advantage of using SUMIFS is that you do not need to search for each product individually. If you have a list of products,
= SUM( SUMIFS( Sales[Amount], Sales[Sales Person], Employee, Sales[Category], productList ) )SUMIFS will return an array, which is then summed for an individual Employee.
Other differences include converting the raw data to a table and using defined names for the list of products and an individual employee.
I would like to offer you two alternative solutions.
All Excel versions: SUMIFS():
=SUMIFS('Raw data'!M$2:M$26,'Raw data'!G$2:G$26,B2,'Raw data'!J$2:J$26,'ST Products'!A$2)+SUMIFS('Raw data'!M$2:M$26,'Raw data'!G$2:G$26,B2,'Raw data'!J$2:J$26,'ST Products'!A$3)+SUMIFS('Raw data'!M$2:M$26,'Raw data'!G$2:G$26,B2,'Raw data'!J$2:J$26,'ST Products'!A$4)
or with EXCEL 365: FILTER() und SUM():
=SUM(FILTER('Raw data'!M$2:M$26,('Raw data'!G$2:G$26=B2)*(('Raw data'!J$2:J$26='ST Products'!A2)+('Raw data'!J$2:J$26='ST Products'!A3)+('Raw data'!J$2:J$26='ST Products'!A4)),0))
- Thanks man.. It actually work thanks alot.
