Sep 29 2022 12:35 AM
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.
Sep 29 2022 09:42 AM
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))
Sep 29 2022 11:08 AM
=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.
Sep 29 2022 12:34 PM - edited Sep 29 2022 12:34 PM
SolutionAn 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.
Sep 29 2022 08:34 PM
Sep 29 2022 08:35 PM
Sep 29 2022 09:10 PM
Sep 30 2022 02:41 AM
Sep 30 2022 10:16 AM
Sep 29 2022 12:34 PM - edited Sep 29 2022 12:34 PM
SolutionAn 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.