I'm having some difficulty referencing my SUM(IF formulas that have multiple criteria to an external workbook that contains multiple sheets. Currently I am able to link to an individual sheet with this:
=SUM(IF(('X:\MAM\Tracking\Yearly Reports\[2014 Report.xlsm]Jan'!$D$7:$D$2000="Criteria A")*....etc
I would like to make a cumulative formula with a reference that spans the entire year (January to December), and I've have luck with this on simple =SUM formulas, but no luck with complex SUM(IFs with multiple criteria. This is what I'm working with and not having much luck.
=SUM(IF(('X:\MAM\Tracking\Yearly Reports\[2014 Report.xlsm]Jan:Dec'!$D$7:$D$2000="Criteria A")*('X:\MAM\Tracking\Yearly Reports\[2014 Report.xlsm]Jan:Dec'!$F$7:$F$2000="Criteria B"),'X:\MAM
\Tracking\Yearly Reports\[2014 Report.xlsm]Jan:Dec'!$O$7:$O$2000))
I could always add 12 SUM(IFs for each month, but that would be a complex solution. I'm hoping you guys have some simple recommendations. Thank's in advance for your help. It's very much appreciated!
….An additional question that comes from this: If I was able to complete this formula and it was functional, could I create similar formulas that reference workbooks/sheets that are not yet created. Example: 'X:\MAM\Tracking\Yearly Reports\[2015 Report.xlsm]Jan:Dec'!. Even though the 2015 Report has not been created, will this formula take effect once it is? Assuming the naming conventions/path is identical.
Thanks again,
- Chris
=SUM(IF(('X:\MAM\Tracking\Yearly Reports\[2014 Report.xlsm]Jan'!$D$7:$D$2000="Criteria A")*....etc
I would like to make a cumulative formula with a reference that spans the entire year (January to December), and I've have luck with this on simple =SUM formulas, but no luck with complex SUM(IFs with multiple criteria. This is what I'm working with and not having much luck.
=SUM(IF(('X:\MAM\Tracking\Yearly Reports\[2014 Report.xlsm]Jan:Dec'!$D$7:$D$2000="Criteria A")*('X:\MAM\Tracking\Yearly Reports\[2014 Report.xlsm]Jan:Dec'!$F$7:$F$2000="Criteria B"),'X:\MAM
\Tracking\Yearly Reports\[2014 Report.xlsm]Jan:Dec'!$O$7:$O$2000))
I could always add 12 SUM(IFs for each month, but that would be a complex solution. I'm hoping you guys have some simple recommendations. Thank's in advance for your help. It's very much appreciated!
….An additional question that comes from this: If I was able to complete this formula and it was functional, could I create similar formulas that reference workbooks/sheets that are not yet created. Example: 'X:\MAM\Tracking\Yearly Reports\[2015 Report.xlsm]Jan:Dec'!. Even though the 2015 Report has not been created, will this formula take effect once it is? Assuming the naming conventions/path is identical.
Thanks again,
- Chris