Forum Discussion
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 based on the ST PRODUCT.
There is multiple categories available in ST Products sheet.
Now I want to sum all the Amount (which is in Raw Data) of a particular salesman who has sold ST Products only..
If the salesman sold a product which category belongs to ST PRODUCTS Sheet data then sum the amount and so on.
Please help me to get rid of this.
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.
8 Replies
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.
- Thanks you got your point.. Can you tell me how do i same if i want to get the total amount of salesman but excluding ST Products this time.
- You would either need a complete list of products to include or use the figure you already have and subtract it from the total sales for the salesman.
=SUMPRODUCT(('Raw data'!M$2:M$26)*('Raw data'!G$2:G$26=B2)*(('Raw data'!J$2:J$26='ST Products'!A$2)+('Raw data'!J$2:J$26='ST Products'!A$3)+('Raw data'!J$2:J$26='ST Products'!A$4)))An alternative could be SUMPRODUCT.
- Thankyou so much for your response i got the answer. thanks again
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.
