Insert Function shows result, but formula doesn't work

Question

When I use 'Insert Function' to review a formula, it shows a result under 'Formula Result'.  However, when I hit Enter, it results in "#VALUE".  Why would the 'Insert Formula' function be able to return a result when the formula in the cell won't?

If it helps, the formula is

=SUM(IF(A19=G5:G6,H5:H6,0))

where A19, G5, G6, H5, and H6 all contain numbers.

Answer 1

The formula you're using there is an array formula which needs to be committed with Ctrl+Shift+Enter.

Why not just use SUMIF instead:

=SUMIF(G5:G6,A19,H5:H6)

comitted normally with Enter.

Hope that helps.

Cheers

Rich

Answer 2

… when I hit Enter, it results in "#VALUE" …

=SUM(IF(A19=G5:G6,H5:H6,0))

This is an array style formula that requires Ctrl+Shift+Enter↵ rather than just Enter↵. You would be better served with a SUMIF(…) formula like this,

=SUMIF(G5:G6,A19,H5:H6)

This require a simple Enter↵.

SUM function
IF function
SUMIF function
SUMIFS function

Introducing array formulas in Excel
Guidelines and examples of array formulas

Answer 3

Gents,

 

Some built-in functions do not yield results. I have not tested all, but when I input Hex2Bin or some other similar conversion functions, there is no result displayed.

 

Thanks and regards,

 

Abdelmalek

 

 

Answer 4

Hi Jeeped, you just beat me to it with this one... but one note: the links you posted for "Introducing array formulas in Excel" and "Guidelines and examples of array formulas" are in French!  (and the first of these two links is broken).

Cheers

Rich