Formulas that work in Office 365 but not in Office 2016

B11 = 100+20 (the senior staff has done the first review and no one will be doing second review)

C11 = 200+600+300+400+150+350+250+450+650+90+60+10+50 (the junior staff has done the first review and the senior staff does the second review)

D11 = 0

E11 = 200+600+300+400+150+350+250+450+650+90+60+10+50 (the junior staff has done the first review and the senior staff does the second review)

Basically, the answers for the above four cells are calculated as shown.
I have set forumlas in those four cells. They work correctly in Office 365 but not Office 2016 (Excel 2016 on Windows (16.0.5356.1000)).
The formulas give #VALUE! when I open the file with Excel 2016.

Is there anyway to amend the formlulas so they work on Office 2016 as well?

There is nothing in the formulae in your attachment that is new: they should all work with Excel 2016.

For example:

=SUMPRODUCT(IFNA(IF(INDEX($K$1:$L$6,MATCH(IF(ISNUMBER(SEARCH(",",$B$3:$B$7,1))=FALSE,$B$3:$B$7,LEFT($B$3:$B$7,FIND(",",$B$3:$B$7)-1)),$K$1:$K$6,0),2)="Senior",$C$3:$C$7),0))+SUMPRODUCT(IFNA(IF(INDEX($K$1:$L$6,MATCH(IF(ISNUMBER(SEARCH(",",$E$3:$E$7,1))=FALSE,$E$3:$E$7,LEFT($E$3:$E$7,FIND(",",$E$3:$E$7)-1)),$K$1:$K$6,0),2)="Senior",$F$3:$F$7),0))+SUMPRODUCT(IFNA(IF(INDEX($K$1:$L$6,MATCH(IF(ISNUMBER(SEARCH(",",$H$3:$H$7,1))=FALSE,$H$3:$H$7,LEFT($H$3:$H$7,FIND(",",$H$3:$H$7)-1)),$K$1:$K$6,0),2)="Senior",$I$3:$I$7),0))

There are NO new functions here.

On reflection, you may need to enter these as array formulae.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

I repeat: there is NOTHING in those four formulae that is incompatible with Excel 2016. In fact, all of the functions were available back in Excel 2007, I believe. Are you sure you are not using something MUCH older?

Furthermore: there is nothing in your sample workbook that wants to spill here in MS365, so I really don't see what I can do to help - sorry.

By the way, why are you opening files in compatibility mode, anyway???

Hopefully someone with Excel 2016 can take a look - I'll put out a call for help.

Interim reply. Does this work in C12, in your version:

=SUMPRODUCT((--ISNUMBER(SEARCH(",",T(+B3:H7))))*(N(+C3:I7)))

@Greg M

On my side it still gives #VALUE!
The Excel 2016 I use is 64 bit. Will this be a possible factor for the error?

in your original attachment (B11), you have three nested IF(INDEX($K$1:$L$6... formulas. Each one is missing the "value-if-false" argument.

Thanks! How should it be fixed? Strangely, the formula works all right in Office 365 even if the value-if-false argumet is missed.

Can the other three cells be completed using this approach as well?

Did you try this yourself? Always give it a go first and then come back and ask if you can't make it work.