SUMPRODUCT & Array Formula Error

[Rockwell_BWM]

Hello,
I have been trying to solve a bit of a mystery with a single-cell formula that includes a SUMPRODUCT function with an array.

The issue: The formula returns a result on three different computers, but, on a fourth user's computer, the formula results in an error.
Additional details: All three users for which the formula works are using Office 365 ProPlus. The fourth user was using Office 2016. Even though 2016 seems should recognize SUMPRODUCT, the fourth user updated to Office 365 ProPlus, however, even after the update, the formula still doesn't work for the fourth user.

The formula:
=IFERROR(IF(OR(B4="", C4=""), "-", "S"&RIGHT(YEAR(C4), 1)&TEXT(MONTH(C4), "00")&VLOOKUP(B4, ServiceTechs, 2, FALSE)&TEXT(SUMPRODUCT((INDIRECT("$B$4:$B$"&ROW(B4))=B4),(MONTH(INDIRECT("$C$4:$C$"&ROW(B4)))=MONTH(C4))),"00")), "-")

The relevant part of the formula is:
=TEXT(SUMPRODUCT((INDIRECT("$B$4:$B$"&ROW(B4))=B4),(MONTH(INDIRECT("$C$4:$C$"&ROW(B4)))=MONTH(C4))),"00"))

Column B contains a list of names
Column C contains a list of dates

The formula is meant to return text-formatted 2-digit count of the occurrence of a specific person in a column (column B) in a given month (extracted from the date column C). This length of the arrays are from the row of the firsdt entry (row 4) until the row that the formula appears in.

This formula works for three users, but not the fourth. After matching versions of Office and checking settings, the only difference noticed is that the formula on the fourth person's version of Office is enclosed by array brackets {}. These array brackets seem too have showed up automatically on this person's version. When creating the formula on my computer, Ctrl+Shift+Enter was not pressed, and they do not appear for those users for whom the formula works.

Can anyone shed some light on this? Thanks!

 

[KRice]

That is puzzling. For the problematic example, have you tried editing the formula...select the cell containing the formula, then go to the formula bar and position the cursor at the end of the formula and hit backspace to delete the character...and then replace that character and hit Enter. Does it still then behave as an array formula?

 

[KRice]

This may be related to a dynamic array feature in Excel 365...

Dynamic array formulas in non-dynamic aware Excel - Microsoft Support

Learn about dynamic array formulas in non-dynamic array aware Excel

support.microsoft.com

 

[Rockwell_BWM]

That is puzzling. For the problematic example, have you tried editing the formula...select the cell containing the formula, then go to the formula bar and position the cursor at the end of the formula and hit backspace to delete the character...and then replace that character and hit Enter. Does it still then behave as an array formula?

I did go onto the PC that is experiencing this issue. I selected the cell with the formula, and, when I did, it appears in the formula bar with the {} around it. However, when clicking into the formula bar, the {} brackets disappear. After pressing just Enter, the {} brackets reappear. I have not tried deleting and re-entering anything in the formula though.

I need to clarify the formula from the one above. The actual formula I originally had that works is:
=TEXT(SUMPRODUCT((INDIRECT("$B$4:$B$"&ROW(B4))=B4)*(MONTH(INDIRECT("$C$4:$C$"&ROW(B4)))=MONTH(C4))),"00"))

The original SUMPRODUCT contained a "*" instead of the comma. The comma was entered while testing to see if I could find the problem.

 

[Rockwell_BWM]

I also just tried THIS.

"In Excel 365 builds that already have the new Dynamic Array formulas, all formulas are treated as array formulas by default. The @ sign is used to prevent the new default array behavior of a function if it is not wanted in that particular formula. "

I used the @ symbol, however, this resulted in a #name error for the user.

I think I narrowed it down to a Version issue. The user updated to Office 365, but they are at Version 1908. According to THIS website, dynamic arrays should be available in Version 1907, however, there are many reports of that version not recognizing dynamic arrays.

The user who is having an issue tried to view the spreadsheet on her laptop, and the formula works. Her laptop has Version 2003 installed. It seems to be an issue with versions prior to v2003, at least.