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