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

Occasional Contributor

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.


Screenshot (198).pngScreenshot (199).pngScreenshot (200).png 

8 Replies


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



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


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(
      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 man.. It actually work thanks alot.
Thankyou so much for your response i got the answer. thanks again
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.