Get total value from different sheet for a specific Cell value.

Occasional Contributor

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

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

All the products come in GT Except that red highlighted text i.e Zopper Extended Warranty that comes in ST. (Note- There are multiple types of Zopper Products are are comes in ST)


Now the question is 

I want to find the 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. 


Raw Data (Sheet 2)

Screenshot (195).png

Sheet 1 (Employee Details)

Screenshot (196).png

1 Reply
best response confirmed by Shaqibiqbal007 (Occasional Contributor)


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

You can try SUMPRODCT.


raw data.JPG


 Employee Details

employee details.JPG