Get sum of particular item of total amount from one sheet to another sheet

[Shaqibiqbal007]

Hey excel family,

Hope you guys help me to get out of this problem.

I am attaching 2 different sheets.... sheet1 named (Employee_Details) and sheet 2 named (Raw Data).

..

Sheet 1-

I am trying to find the total value of GT act & ST Act through the employee name &  Prod name from the raw data sheet (Sheet 2) where the sales amount is available.

GT Target & ST Target is given in Sheet 1

Sheet 2

All the products come in GT except that red highlighted text which comes in ST.

...

Now I want to get the total amount of GT & ST for a particular salesman into the GT Act & ST Act. (Sheet 1). So that I get to know whether he completed his GT & ST Target or not.

Please have a look guys and get me rid out of this problem. Thankyou in Advance

[#2]

Hello @Shaqibiqbal007

You said "I am attaching 2 different sheets.... sheet1 named (Employee_Details) and sheet 2 named (Raw Data)." yet you sent us an image of the worksheet "Testing"???

OK can you please send the correct worksheets with highlighting the areas you want us to look at. Make a new testing copy of your workbook, REMOVE all shadings, font colors, or what have you and simply use soft colors to highlight the areas you want us to look at.

Possibly write the formula in words in a cell for example:

I want to add all the available values in Column B where the value in Column J is equal to "Boy" and then I want to subtract all values in Column C where Column P has the value "Girl"

If you can do that we can better help you.

Georgie Anne

[#3]

@GeorgieAnne 

Sheet 1 i.e Employee Details & Sheet 2 i.e Raw Data.

In the Raw data sheet, there is two types of products GT & ST.

All the products come in GT Except that red highlighted text i.e Zopper Extended Warranty that come in ST.

Now the question is 

I want to find that total sales amount of GT & ST in GT & ST Value Column (Employee Details Sheet) for a particular salesman with the help of the Raw Data Sheet where all the sales and their amount are available. so that I could compare whether he completed his GT & ST target or not. 

[#4 · Accepted solution]

@Shaqibiqbal007 

=SUMPRODUCT(ISNUMBER(SEARCH("Zopper Extended Warranty",'Raw Data'!$H$2:$H$14))*('Raw Data'!$G$2:$G$14=A2)*'Raw Data'!$M$2:$M$14)

Maybe with the SUMPRODUCT function.

Raw Data

Employee Details

[#5]

Hey.. Thankyou so much for the help.. It works for me
I just want to ask you one more question that similarly how to find total GT Value that exclude "Zopper Extended Warranty".. what will be the formula?

[#6]

@Shaqibiqbal007 

You are welcome.

=SUMPRODUCT(NOT(ISNUMBER(SEARCH("Zopper Extended Warranty",'Raw Data'!$H$2:$H$14)))*('Raw Data'!$G$2:$G$14=A2)*'Raw Data'!$M$2:$M$14)

You can apply this SUMPRODUCT formula.

Raw Data

Employee Details

[#7]

Hey thankssss alot man can you please tell me =SUMPRODUCT(SUMIFS(L7:L11,J7:J11,C9,K7:K11,D9)) how to apply not function is this formula i am doing same but getting error