Am I Going Nuts ?

[steve38]

Hi,
Im a little new to excel and formulas but I think I'm going nuts, I have a spreadsheet for totaling lengths of rope from different coils... i add the coil lengths then mix and match the required lengths to get the most efficient use of the coils.Column D holds the needed lengths Column F is where I input the coil i want to use,for example for coil number 6 it totals the amount used from each coil using "=SUM(IF(F5:F97=6,D5:D97,0))" which works ok but the next formula for coil number 7 "=SUM(IF(F5:F97=7,D5:D97,0))" returns nothing, I cant see the difference at all but one works one doesnt ... any ideas ?
Cheers
Steve.

 

[barry houdini]

Hello steve38,

Those are "array formulas" which you need to confirm with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar. If you don't do that you may get incorrect results.

You can use a "non-array" SUMIF more easily, i.e.

=SUMIF(F5:F97,7,D5:D97)

 

[steve38]

Barry,
Thanks... I knew it must be something simple and I did did seem to recall something like that but its that rare that I play with Excel I wasnt sure, though i did try ctrl+enter it didnt work so thought i was imagining I had to do something different.
You are a star ... and maybe Im just an idiot .. but thanks a lot.
Cheers
Steve.