# CountIFS For Linked workbook

Copper Contributor

# CountIFS For Linked workbook

Hello,
I'm trying to bring some data using the "CountIFS" formula into linked books, but the formula seems to don't work; it returns results 0. Still, it should reflect 1+, I was reading a bit, and it looks like I should replace the formula with another different, any ideas?

14 Replies

# Re: CountIFS For Linked workbook

You need to explain more fully what the column contains are that you're seeking to count. Ideally, post a copy of the workbook on OneDrive or GoogleDrive and paste a link here that grants edit access. Your current description is too brief for anybody to offer any specific advice.

# Re: CountIFS For Linked workbook

Thanks for the recommendation; since this is a corporate document, I cannot share it publicly, but I will try to provide more details!

I have a Workbook with information filled by our team members ("Sourcebook"); I want that information in a different Excel book ("Destination book") for management purposes, so I created a new file to include the information entered by the team within the same shared online folder.

When I'm trying to feed the "destination book" from "Sourcebook" with formulas such as CountIFS it returns a #VALUE error. I checked all data in the "source book" and couldn't identify any mistakes.

I also used formulas like Sum(SumIFS, Sum(If as per microsoft community recommendations. (https://learn.microsoft.com/en-us/office/troubleshoot/excel/formula-returns-value-error)

Thanks in advance for any help you could provide.
Regards,

# Re: CountIFS For Linked workbook

it is usually something like character strings with different lengths and numbers as text on sheet and as numbers on the other

# Re: CountIFS For Linked workbook

Some functions cannot retrieve data from a closed workbook.  COUNTIFS is among the functions requiring the external workbook be open.

Functions that Can Access Closed Workbooks (Microsoft Excel) (tips.net)

# Re: CountIFS For Linked workbook

Thank you,
But both books are open while I'm working on them, "Sourcebook" is active.

# Re: CountIFS For Linked workbook

I see!
Would you suggest changing the wording? I'm trying to get all results with criteria "YES" and "Q1" from my sourcebook.

# Re: CountIFS For Linked workbook

Are you able to share the formula(s) you're using, the one(s) that don't work as desired? Even if you can't share the workbook itself, seeing the actual formula could help in our shared diagnosis.

# Re: CountIFS For Linked workbook

@mathetes Resharing, Thank you

# Re: CountIFS For Linked workbook

What values are in \$D\$2:\$D\$13 and \$E\$2:\$E\$13?  Can you display an image showing those, blocking out the other fields?

Is it possible that the mismatch is between the data in those columns and the criteria in the formula?

# Re: CountIFS For Linked workbook

@mathetes Sure :) here is the data.

I verified a couple of times to see if there were something mistyped, but couldn't locate any error on the file :(

# Re: CountIFS For Linked workbook

Nothing pops out at me.

I did wonder if you've tried plain COUNTIF (with out the "S" on the end).

Also I'm wondering whether you have a new enough version of Excel for the FILTER function to work? It requires 2021 or newer, or the Microsoft 365 subscription.

FILTER can work with multiple criteria and can be nested within a COUNT or COUNTA formula. Here's a YouTube video that introduces FILTER and some other dynamic array functions.

# Re: CountIFS For Linked workbook

Thanks for the guidance and advices, just wondering if COUNTIF will provide the results I need, since I'm adding two different criteria!
Thank you

# Re: CountIFS For Linked workbook

That's why I suggested the COUNT(FILTER) combination as ideal

# Re: CountIFS For Linked workbook

See the attached example of both a COUNT and a SUM with a FILTER function nested within. Assuming you have software that can handle these "dynamic array functions" you can change the entries in the yellow cell to see how the formulas respond. You can also extend the table, by just adding rows, to see how it will continue to track. (keep the same range of values in those two columns)