SUMPRODUCT & Array Formula Error

Rockwell_BWM

New Member
Joined
Apr 21, 2020
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
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!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,134
Office Version
  1. 2019
Platform
  1. Windows
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?
 

Rockwell_BWM

New Member
Joined
Apr 21, 2020
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Apr 21, 2020
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,914
Messages
5,655,937
Members
418,253
Latest member
TheJackal26

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
Top