Sumifs across workbooks

[Flytallguy83]

Hello,
I am trying to use sumifs formulas across workbooks. I have read a couple of posts about this and wrote vb code to open each workbooks that my sumifs formulas reference. Even after I open and close each referenced workbook though, I still have #VALUE errors. I am using variables in my file paths when opening the referenced workbooks. Does anyone know what I could be doing wrong? If I manually open my referenced workbooks, my sumifs formulas calculate correctly.

 

[Domenic]

Use SUMPRODUCT instead...

=SUMPRODUCT(--(RangeX=CondX),--(RangeY=CondY),RangeToSum)

 

[Flytallguy83]

Thanks Domenic,
Maybe I am not familiar enough with sumproduct formulas, but I do not wish to multiply anything. Here is my sumifs formula, I have tried putting it in your suggested sumproduct format, but keep resulting in 0's.

=SUMIFS([307EOM.xlsm]REC_SHIP!$I:$I,[307EOM.xlsm]REC_SHIP!$A:$A,LEFT(RIGHT(CELL("FILENAME",$A$1),8),2),[307EOM.xlsm]REC_SHIP!$F:$F,"I",[307EOM.xlsm]REC_SHIP!$M:$M,"R")

 

[Flytallguy83]

I just got it to work! Thanks again Domenic!

 

[stuartmacdonald]

Hi Dominic

I'm trying to sum a value of a column where the conditions in two ranges match.
This is across workbooks so I need to use SUMPRODUCT. I'm using the example you posted above but I'm unclear what to enter for 'CondX/CondY'. Below is my example with the 3 ranges entered

=SUMPRODUCT(--($B$4:$B$48=CondX),--([Timesheet_NM.xlsx]Projects_NM!$A$2:$A$100=CondY),[Timesheet_NM.xlsx]Projects_NM!$S$2:$S$100)

Feel like I have missed something basic here...

Thanks in advance