Share via

=IF(COUNTIF) formula between two workbooks works when using the desktop application but breaks when using web application (returns a #VALUE error)

Anonymous
2023-05-31T13:07:15+00:00

Hi,

I have two Excel files; File A and File B. They are saved to SharePoint. I edit the files with the Excel desktop client (I sync the files via OneDrive).

In File A I use the formula =IF(COUNTIF(!$R:$R, "Text string 1"), "Text", IF(COUNTIF(!$R:$R, "Text string 2"), "Text", IF(COUNTIF(!$R:$R, "Text string 3"), "Text", ""))) and it checks the column R of File B. When I have both files open in the desktop client the formula works.

When I close both files Excel converts the formula to:

=IF(COUNTIF('[Filename on Sharepoint]Workbook'!$R:$R, "Text string 1"), "Text", IF(COUNTIF('[Filename on Sharepoint]Workbook'!$R:$R, "Text string 2"), "Text", IF(COUNTIF('[Filename on Sharepoint]Workbook' !$R:$R, "Text string 3"), "Text", "")))

Probably because the files are saved on SharePoint.

However when I open file A (either in the Desktop client or the online version) the formula no longer works. It returns a #VALUE error.

Does someone know why the formula breaks and how I can fix this?

I came across a similar question (https://answers.microsoft.com/en-us/msoffice/forum/all/excel-online-workbook-references-within-sharepoint/e35d58d7-3d8f-48d1-aa4e-907cf3eb0450), but the proposed solution does not work for me. The SUM function doesn't work for text strings (as far as I know).

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-05-31T16:09:03+00:00

    From the previous post. the solution should be.

    =SUM(IF('[Filename on Sharepoint]Workbook'!$R:$R="Text string 1",1,0))

    If you meet the same issue,

    Cause: This behavior occurs when the formula that contains the function refers to cells in a closed workbook and the cells are calculated.

    Formula returns - Office | Microsoft Learn

    COUNTIF

    Instead of using a formula that is similar to the following:

    =COUNTIF([Source]Sheet1!$A$1:$A$8,"a")

    use the following formula:

    =SUM(IF([Source]Sheet1!$A$1:$A$8="a",1,0))

    Was this answer helpful?

    3 people found this answer helpful.
    0 comments