COUNTIFS & SUMIFS from closed workbook(s)

bennyx_o

New Member
Joined
Apr 22, 2016
Messages
5
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!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Wanted to add - I'm looking for it to pull the data without opening the other workbooks (It will eventually pull from 4 or 5 different ones, all which contain) I'm hoping to have A1 being a drop down with everyones name so it's easy to change between people to see information
 
Upvote 0
Apologies to post again - I've got the first oneto work, however, I can't get it to add the data – it just returns 0. Below isa sample of the data in the sheet;


A

B
C
D
E
F
G

H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Branch Name
Account Number
Application Status

Decision

Primary Applicant Full Name
Number Of Applicants
Application Type
Amount
Application Owner
Decision Maker
Received Date
Completion Date
Approval Date
Declined Date
iHelpPathName
Week
Month
Credit
Branch
Channel
Online Score
Weekday
Day
Drawdown
Internet
1234567
Received
Declined
Test Test
1
1500
Test Agent
01/01/2018
Term Loan
1
1
Open24 Web User
990660
Online
#N/A

1
Monday
1500


<tbody>
</tbody>

Columns P – X are formulas which pulls the information fromColumns A-O (i.e P is = WEEKNUM(K2,2),"")) I’m unsure if this hasanything to do with it not adding up.
Any help is appreciated!
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top