Sumproduct question - compare two lists

[bcfaigg]

Folks,
Hopefully a simple query. In simple terms, I have a large list (circa 40k) of timesheet entries (in a worksheet named 'Timesheet Entries') with three columns:
a) Column A: Staff Name
b) Column B: Time (hours)
c) Column C: Hourly Rate

In a separate worksheet ('Report' / Column A), I have a list of Staff Names, and want to add up (sum) the revenue from all staff within that list.

Essentially, something like:
=SUMPRODUCT('Timesheet Entries'!$B$1:$B$50000,'Timesheet Entries'!$C$1:$C$50000,--('Timesheet Entries'!$A$1:$A$50000='Report'!$A$1:$A$12))

I know the first two clauses work correctly - i.e. if I remove the --('Timesheet Entries...) clause then it correctly calculates the overall revenue for the entire company.

Similarly, I can make it work for a single individual - i.e. change the third clause to --('Timesheet Entries'!$A$1:$A$50000="Bob") and it correctly calculates Bob's revenue. Clearly, I could use calculate revenue for each staff number in turn, and then add them all up - but I was hoping for a more elegant solution.

Does anyone have any bright ideas of what I need to tweak?

Thanks
Bcfaigg

 

[Special-K99]

Is it possible to put a "range=range" in a SUMPRODUCT? I'm using the other format of SUMPRODUCT and am getting #N/A for ('Timesheet Entries'!$A$1:$A$50000='Report'!$A$1:$A$12)
range=single cell, ie Bob works fine of course.
I cant recall seeing a range=range in a SUMPRODUCT.

 

[NARAYANK991]

Hi ,

See if this works :

=SUMPRODUCT(COUNTIF(Report!$A$1:$A$12,'Timesheet Entries'!$A$2:$A$50000), ('Timesheet Entries'!$B$2:$B$50000) * ('Timesheet Entries'!$C$2:$C$50000))

 

[bcfaigg]

That did indeed work. I found a separate solution which also works:
=SUMPRODUCT(--(ISNUMBER(MATCH('Timesheet Entries'!$A$1:$A$50000,'Report'!$A$1:$A$12,0))),('Timesheet Entries'!$B$2:$B$50000),('Timesheet Entries'!$C$2:$C$50000))

Thanks for your help.
Bcfaigg