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

[Shaqibiqbal007]

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.

 

[#2]

@Shaqibiqbal007 

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

[#3]

@Shaqibiqbal007 

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

[#4 · Accepted solution]

@Shaqibiqbal007 

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.

[#5]

Thanks man.. It actually work thanks alot.

[#6]

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

[#7]

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.

[#8]

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.

[#9]

omg thankkkss alottt sir..