Sumproduct question - compare two lists

bcfaigg

Board Regular
Joined
Dec 1, 2005
Messages
78
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.
 
Last edited:
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top