Thread: Sumproduct + vlookup

Hi,

I want some one to help me with SUMPRODUCT.
I want in the summary sheet The count should be
Detailsheet.Status = A And Detailsheet.Work=CB And as Detailsheet.APP (X1) belong to Controlsheet.Team(Team1) the count should be as below in Summary Sheet.

Summary Sheet
Work Team Count
CB Team1 1
CB Team5 2

Detail Sheet
Status Work App
A CB X1
Y CB X2
A MM X3
Y MM X4
A CB Y1
Y CB X2
A MM X3
Y MM X4
A CB Z1

Control Sheet
APP Team
X1 Team1
X2 Team2
X3 Team3
X4 Team4
Y1 Team5
Z1 Team5

ankurshah77,

Attached is an example workbook based on the criteria you described. In the Detail sheet, I added a fourth column, 'Team', and it uses a vlookup formula to get the Team based on the App. Then, I used the Detail table to create a pivot table in the Summary sheet.

Try Formula in C2 of Summary sheet:

=SUMPRODUCT((Detail!$B$2:$B$10=A2)*ISNUMBER(MATCH(Detail!$C$2:$C$10&B2, Control!$A$2:$A$7&Control!$B$2:$B$7,0)))

copied down.

Assuming tables in each sheet are in top left corner starting at A1.