Countifs, Averageifs, Sumifs for a closed workbook

[jomili]

I've attached my workbook. In a nutshell, I'm trying to use the IFS family of functions to pull information from a closed workbook, and failing because IFS functions don't work on closed workbooks. I want to know if there are any workarounds that I can use in this application.

There are many different formula types, so I'm not going to put them all in this post, but here's one sample formula:

Please Login or Register  to view this content.

, where C3 is "Lynn", and F:F contains "Lynn"

The Dashboard has to be separate from the workbook because it has a different audience than the one that uses the workbook. I'd prefer NOT to duplicate the workbook, because it has multiple tabs and is pretty slow.

Any help would be greatly appreciated.

[Bo_Ry]

Please change to Sumproduct and don't use the whole column.
B5
=Sumproduct(--('\\12aust1001fs01\share10011\Accounting\Z4116\[4116 Tracker.xlsx]IV-E Pymts'!$F$1:$F$999=B$3)

B6
=IFERROR(Sumproduct('\\12aust1001fs01\share10011\Accounting\Z4116\[4116 Tracker.xlsx]IV-E Pymts'!$X$1:$X$999,--('\\12aust1001fs01\share10011\Accounting\Z4116\[4116 Tracker.xlsx]IV-E Pymts'!$F$1:$F$999=B$3))/B5,0)

[jomili]

I was just researching Sumproduct, and had come up with the same formula for B5 , but WAS using the whole column. I'll change that.

My next step was trying to tackle B6, but you already solved that. Thanks so much. I'll keep working on the rest and come back if I run into a snag.

[jomili]

I'm making progress, but stuck on C21. The formula I'm trying to use is

Please Login or Register  to view this content.

, but it's resolving to 0, when it should be 18. Is it the Wildcard? How would I do this one?

[Bo_Ry]

Please try
=sumproduct(--('\\12aust1001fs01\share10011\Accounting\Z4116\[4116 Tracker.xlsx]FY 20 Reqs'!$G$1:$G$5000=C$3),--isnumber(search("Translation*",'\\12aust1001fs01\share10011\Accounting\Z4116\[4116 Tracker.xlsx]FY 20 Reqs'!$D$1:$D$5000)))

[jomili]

Thanks Bo_Ry, I had come up with a similar solution, which gets me past C21. Now I'm on C22, and I can't get the correct answer. My original formula was

Please Login or Register  to view this content.

The formula I'm attempting is

Please Login or Register  to view this content.

, which ~works. Gives me 5.33 (wrong value) instead of 3.56 (correct value). What am I doing wrong?

[jomili]

Never mind, I found it. My formula was right, my reference was wrong. Should have been C21, not C13. That's what I get for looking too closely at earlier formula without taking the high level view.

[jomili]

Gosh, this is like pulling teeth! Now I'm on C23. The formula I'm trying is [CODE=SUMPRODUCT(--ISNUMBER(SEARCH("Translation",'[4116 Tracker.xlsx]Please Login or Register to view this content.[/CODE], which is resolving to 67,765.03, when it should be 576.71. What do I have wrong now?

[jomili]

Never mind (again!). "FCL Monitor Tracking" instead of "FY 20 Reqs". GRRR! Dumb errors kill me!

[jomili]

Okay, I've got almost everything resolved. My last issue isn't an "IFS" issue, it's an OFFSET/Counta issue.

In G20 and G28 I'm trying to bring in the Earliest dates for my Translation and Travel vouchers, which are subsets of FY20 Reqs sheet. I have three named ranges, "Date_to_AP", "Date_to_CA", and "Descr". These refer to ranges in the 4116 Tracking workbook. Their range formulas are like this:

Please Login or Register  to view this content.

If the 4116 tracking workbook is closed they resolve to an error.

My formula in G20 is simply supposed to bring in the earliest day for a translation voucher, and the formula uses the named range, for instance:

Please Login or Register  to view this content.

How can I accomplish my goal?

[Bo_Ry]

You can use fixed range instead of named ranges Date_to_CA and Descr
eg; '\\12aust1001fs01\share10011\Accounting\Z4116\[4116 Tracker.xlsx]FY 20 Reqs'!$H$1:$H$5000

[jomili]

BO-Ry,
When I try that, my Aggregate formula resolves to a #NUM error, unless the source workbook is open. How do I get around that?

[jomili]

I found it. I hadn't yet updated all three references. Now I've done that and it appears to be working beautifully. Thanks so much for sticking with me through this, you've helped me tremendously.