Share via

SUMIF between workbooks

Anonymous
2017-06-26T04:22:06+00:00

I have a workbook where one worksheet has employee numbers and hours they're working on a specific project. One employee can have multiple rows when they work on more than one project.

Another worksheet uses the formula below to total all hours for an employee so I can see over and under allocation. In this example the employee number is in column F on the source data, and hours worked is in column R. The employee number has a leading character e.g. A776655

I have a SUMIF formula as follows which works fine:

=SUMIF('Resource Demand'!$F$7:$F$500,$A7,'Resource Demand'!R$7:R$500)

My problem hits when I try to move this formula off to another workbook (note, I've replaced the real path with \\path for readability).

An internet search shows that this works fine as long as Demand MASTER is open, which it will usually not be:

=SUMIF('\path[Demand MASTER.xlsx]Resource Demand'!$F$7:$F$500,$A7,'\path[Demand MASTER.xlsx]Resource Demand'!R$7:R$500)

And I've seen suggestions that this should work as a replacement, but all I get is 0 values:

=SUMPRODUCT(('\path[Demand MASTER.xlsx]Resource Demand'!$F$7:$F$500=$A7),'\path[Demand MASTER.xlsx]Resource Demand'!R$7:R$500)

I don't know if it makes any difference, but these files are stored on a SharePoint site, and they are in the same folder.

Can anyone help please?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2017-06-26T17:50:27+00:00

    My files are HoursWorked and WorkSummary

    The formulas in WorkSummary were created with HoursWorked open

    No need to give path

    Note how SUMIF and SUMPRODUCT give the same results - Deo Gratias!

    Now I close both files and open only WorkSummary

    The path appears in the formulas (Excel knows where the file was saved)

    But whereas SUMPRODUCT gives the results as before, SUMIF returns #VALUE! errors until I open the HoursWorked file

    I try to avoid spaces in workbook and worksheet names

    best wishes

    8 people found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-06-27T00:06:45+00:00

    Thank you so much Bernard. Looks like the only thing I was missing was the -- in front of the first argument. Not sure what it's purpose is, but it's working now.

    1 person found this answer helpful.
    0 comments