Hello,
I'm in the process of building a dashboard type spreadsheet which will pull data from other excel files instead of needing to be manually updated. I'm aware that COUNTIFS & SUMIFS won't work on a closed workbook after a google search, but I can't make head nor tail in getting SUMPRODUCT to work (despite reading countless pages on how it works)
Here is the COUNTIFS I'm trying to use;
=COUNTIFS('S:\path\to\[file.xlsx]Sheet1'!$R:$R,$A$1,'S:\path\to\[file.xlsx]Sheet1'!$Q:$Q,1)
This is the SUMPRODUCT that I think should work but it returns a #VALUE ! error
=SUMPRODUCT(--('S:\path\to\[file.xlsx]Sheet1'!$R:$R="A1")*('S:\path\to\[file.xlsx]Sheet1'!$Q:$Q,1))
For reference, Column R contains a persons name & Column Q contains the month number
I'm also having the same issue with the below SIMIFS
=SUMIFS(''S:\path\to\[file.xlsx]Sheet1'!$X:$X,''S:\path\to\[file.xlsx]Sheet1'!$R:$R,A1,''S:\path\to\[file.xlsx]Sheet1'!$Q:$Q,1)
For reference, X contains an amount, R is the persons Name & Q is the month number
As always, any any help is much appreciated!
I'm in the process of building a dashboard type spreadsheet which will pull data from other excel files instead of needing to be manually updated. I'm aware that COUNTIFS & SUMIFS won't work on a closed workbook after a google search, but I can't make head nor tail in getting SUMPRODUCT to work (despite reading countless pages on how it works)
Here is the COUNTIFS I'm trying to use;
=COUNTIFS('S:\path\to\[file.xlsx]Sheet1'!$R:$R,$A$1,'S:\path\to\[file.xlsx]Sheet1'!$Q:$Q,1)
This is the SUMPRODUCT that I think should work but it returns a #VALUE ! error
=SUMPRODUCT(--('S:\path\to\[file.xlsx]Sheet1'!$R:$R="A1")*('S:\path\to\[file.xlsx]Sheet1'!$Q:$Q,1))
For reference, Column R contains a persons name & Column Q contains the month number
I'm also having the same issue with the below SIMIFS
=SUMIFS(''S:\path\to\[file.xlsx]Sheet1'!$X:$X,''S:\path\to\[file.xlsx]Sheet1'!$R:$R,A1,''S:\path\to\[file.xlsx]Sheet1'!$Q:$Q,1)
For reference, X contains an amount, R is the persons Name & Q is the month number
As always, any any help is much appreciated!