I tried the following two formulas, which should be producing the same value,
but I either get a blank cell or N/A. I have copied this formula and am
positive that it should be working. The same formulas works sometime and not
other times in other cells. Is there anything with sheet calculating or
array functions that I'm missing? Thanks for your help.

{=SUM(IF(('[Rollup - Facility.xls]Scenario'!$A$5:$A$611="311")*('[Rollup -
Facility.xls]Scenario'!$F$5:$F$611="84 (AKASHI)")*('[Rollup -
Facility.xls]Scenario'!$E$5:$E$611="NACD"),'[Rollup -
Facility.xls]Scenario'!$H$5:$H$611))}

{=INDEX('[Rollup -
Facility.xls]Scenario'!$H$5:$H$450,MATCH(1,("NACD"='[Rollup -
Facility.xls]Scenario'!$E$5:$E$450)*("311" = '[Rollup -
Facility.xls]Scenario'!$A$5:$A$450)*("84 (AKASHI)" = '[Rollup -
Facility.xls]Scenario'!$F$5:$F$450),0))}

Is "311" text or numeric in your data set? If it is numeric, try removing
the quotes.

Also, if H5:H611 only contains numbers, you s/b able to remove the IF
statement and get the same results. I would use sumproduct (which accepts
array arguments so no need for CSE):

=SUMPRODUCT(('[Rollup - Facility.xls]Scenario'!$A$5:$A$611=311)*('[Rollup -
Facility.xls]Scenario'!$F$5:$F$611="84 (AKASHI)")*('[Rollup -
Facility.xls]Scenario'!$E$5:$E$611="NACD")*'[Rollup -
Facility.xls]Scenario'!$H$5:$H$611)


"Alex" wrote:

I tried the following two formulas, which should be producing the same value,
but I either get a blank cell or N/A. I have copied this formula and am
positive that it should be working. The same formulas works sometime and not
other times in other cells. Is there anything with sheet calculating or
array functions that I'm missing? Thanks for your help.

{=SUM(IF(('[Rollup - Facility.xls]Scenario'!$A$5:$A$611="311")*('[Rollup -
Facility.xls]Scenario'!$F$5:$F$611="84 (AKASHI)")*('[Rollup -
Facility.xls]Scenario'!$E$5:$E$611="NACD"),'[Rollup -
Facility.xls]Scenario'!$H$5:$H$611))}

{=INDEX('[Rollup -
Facility.xls]Scenario'!$H$5:$H$450,MATCH(1,("NACD"='[Rollup -
Facility.xls]Scenario'!$E$5:$E$450)*("311" = '[Rollup -
Facility.xls]Scenario'!$A$5:$A$450)*("84 (AKASHI)" = '[Rollup -
Facility.xls]Scenario'!$F$5:$F$450),0))}