# Am I Going Nuts ?

#### steve38

##### New Member
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.

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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)

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.

Replies
11
Views
331
Replies
8
Views
210
Replies
0
Views
118
Replies
0
Views
193
Replies
3
Views
146

1,207,390
Messages
6,078,206
Members
446,321
Latest member
thecachingyeti

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back