SOLVED

# Find the total amount value based on different category Products for a specific salesman

Occasional Contributor

# 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.

8 Replies

# Re: Find the total amount value based on different category Products for a specific salesman

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))``

# Re: Find the total amount value based on different category Products for a specific 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.

best response confirmed by Shaqibiqbal007 (Occasional Contributor)
Solution

# Re: Find the total amount value based on different category Products for a specific salesman

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.

# Re: Find the total amount value based on different category Products for a specific salesman

Thanks man.. It actually work thanks alot.

# Re: Find the total amount value based on different category Products for a specific salesman

Thankyou so much for your response i got the answer. thanks again

# Re: Find the total amount value based on different category Products for a specific salesman

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.

# Re: Find the total amount value based on different category Products for a specific salesman

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.

# Re: Find the total amount value based on different category Products for a specific salesman

omg thankkkss alottt sir..