SumProduct Across Multiple Workbooks in Excel

[JimLes]

I have search many forums for an example of a formula that will count text in multiple workbooks. Here is my current formula but it is only giveing me totals from the first workbook.

Current Formula: (needs to count instances of "BR HQ" in 2 workbooks in excel)

=SUMPRODUCT(('R:\HR Ops Team Folders\Reporting & Analysis\Headcount\HC Data\[Transfer Out Division HC Data - US & Canada.xls]Sheet1'!$B$2:$B$1000="BR HQ") + SUMPRODUCT('R:\HR Ops Team Folders\Reporting & Analysis\Headcount\HC Data\[Transfer Out Job Code HC Data - US & Canada.xls]Sheet1'!$B$2:$B$1000="BR HQ"))

Any assistance is appreciated.
Thanks,

 

[GlennUK]

Put the 2 Sumproducts in separate cells, and see which one isn't working, and then try to work out what's going wrong in that particular one.



Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[macropod]

Hi JimLes,

Array formulae like SUMPRODUCT cannot span multiple worksheets. Hence, since the worksheets are in different workbooks, the formula won't work.

Cheers

[MS MVP - Word]

 

[SkipVought]

Since you are just counting occurences of ONE criteria in ONE range, I'd be using the COUNTIF function.

Another alternative, and the one I'd most likely use is to Query these two data sources using MS Query. faq68-5829

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[JimLes]

I did see a post in another forum where someone got it to work.

http://www.archivum.info/microsoft.public.excel.worksheet.functions/2006-07/msg00903.html

Their formula looked like this but I cannot duplicate it.


=(SUMPRODUCT(--('S:\June\[Metzger.xls]MONTHLY REPORTS'!$C$15:$C$24=2))+SUMPRODUCT(--('S:\June\[Opp.xls]MONTHLY REPORTS'!$C$15:$C$24=2))+SUMPRODUCT(--('S:\June\[Burke.xls]MONTHLY REPORTS'!$C$15:$C$24=2)))*2

 

[SkipVought]

FYI,

You have a fundamental problem that I would address first. Once this issues is solved, then doing ANY analysis & reporting will be a snap using excel's features.

Your primary problem is that you have your data, scattered in many different locations. I would address this entire issue by developing a means of CONSOLIDATING your data into a SINGLE DATA SOURCE: probably one sheet; maybe a database like MS Access, depending on the number of rows you will be accumulating. Even if you have to settle for copy & paste, that would be light years better than your current approch. It would open VISTAS (not MS Vista!!!) of opportunities to USE THE DATA, which is a corporate asset. But being store in multiple workbooks, its as good as being filed in a drawer somewhere, IMHO.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Hi JimLes,

In your formula, you've got one SUMPRODUCT expresseion embedded inside another - that means the one SUMPRODUCT formula is trying to span multiple worksheets.

Having said that, though, you don't need SUMPRODUCT for what you're doing - all you need is two SUMIFs:

=SUMIF('R:\HR Ops Team Folders\Reporting & Analysis\Headcount\HC Data\[Transfer Out Division HC Data - US & Canada.xls]Sheet1'!$B$2:$B$1000,"BR HQ") + SUMIF('R:\HR Ops Team Folders\Reporting & Analysis\Headcount\HC Data\[Transfer Out Job Code HC Data - US & Canada.xls]Sheet1'!$B$2:$B$1000,"BR HQ")

If you're wedded to SUMPRODUCT, you could use:

=SUMPRODUCT('R:\HR Ops Team Folders\Reporting & Analysis\Headcount\HC Data\[Transfer Out Division HC Data - US & Canada.xls]Sheet1'!$B$2:$B$1000="BR HQ") + SUMPRODUCT('R:\HR Ops Team Folders\Reporting & Analysis\Headcount\HC Data\[Transfer Out Job Code HC Data - US & Canada.xls]Sheet1'!$B$2:$B$1000="BR HQ")

All you need to do to achieve this is to delete the first & last brackets from your existing formula.

Cheers


[MS MVP - Word]

 

[JimLes]

Thanks Macropod,

You got me on the right track. I don't know why, but this formula did the trick:

=SUM(IF('R:\HR Ops Team Folders\Reporting & Analysis\Headcount\HC Data\[Transfer Out Job Code HC Data - US & Canada.xls]Sheet1'!$B$2:$B$1000="BR HQ",1,0))+SUM(IF('[Transfer Out Division HC Data - US & Canada.xls]Sheet1'!$B$2:$B$1000="BR HQ",1,0))

Thanks to everyone for their assistance!