Formula assembly

User avatar
RonH
SilverLounger
Posts: 1802
Joined: 02 Mar 2010, 16:53
Location: An Aussie in Norway

Formula assembly

Post by RonH »

If I create a formula =SUM(D10:E10:G10:H10) the result also includes F10 entries in the sum. How can I avoid this?

Also I would appreciate help for the following:
1. Formula to calculate K10 + (P10xI10)

2. How can a vertical 12 column auto-formula be made from say =SUM(D10:E10:G10:H10) down to =SUM(D22:E22:G22:H22)

Its quite some time since I tried to create formula in Excel so I am likely also using old and now incorrect formats.
Thanks
CYa Ron
W11 pc, Android toys.
The only reason we have the 4th dimension of Time is so that everything does not happen at once.

User avatar
HansV
Administrator
Posts: 73825
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula assembly

Post by HansV »

0. Use

=SUM(D10:E10,G10:H10)

or

=SUM(D10,E10,G10,H10)

1. Excel uses * for multiplication, so

=K10+P10*I10

2. Select the cell with the formula =SUM(D10:E10,G10:H10).
There is a little black square in the lower right corner of the cell.
Click and drag this down 13 rows.
Alternatively, select the cell with the formula and the 12 cells below it, then press Ctrl+D.
Regards,
Hans

User avatar
RonH
SilverLounger
Posts: 1802
Joined: 02 Mar 2010, 16:53
Location: An Aussie in Norway

Re: Formula assembly

Post by RonH »

Thanks Hans.

Under 2 above, I could not find a little black square but the cell selection plus Ctrl+D worked.
CYa Ron
W11 pc, Android toys.
The only reason we have the 4th dimension of Time is so that everything does not happen at once.

User avatar
HansV
Administrator
Posts: 73825
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula assembly

Post by HansV »

I meant this:

S1029.png

If you don't see that:
- Select File > Options.
- Select Advanced in the navigation pane on the left.
- Make sure that the check box 'Enable fill handle and cell drag-and-drop' is ticked.
- Click OK.

S1030.png
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

User avatar
RonH
SilverLounger
Posts: 1802
Joined: 02 Mar 2010, 16:53
Location: An Aussie in Norway

Re: Formula assembly

Post by RonH »

Thanks again Hans ... somehow this had been de-ticked
Cheers Ron
CYa Ron
W11 pc, Android toys.
The only reason we have the 4th dimension of Time is so that everything does not happen at once.

User avatar
Argus
GoldLounger
Posts: 3081
Joined: 24 Jan 2010, 19:07

Re: Formula assembly

Post by Argus »

HansV wrote:
02 Jan 2022, 14:41
0. Use

=SUM(D10:E10,G10:H10)

or

=SUM(D10,E10,G10,H10)
And to complicate things, in Nordic language versions, or rather, several international versions, and I know Hans knows this, it's not "," but ";" as formula separator (and not SUM, but SUMMA, here). But your question indicated English, Ron. :smile:
Byelingual    When you speak two languages but start losing vocabulary in both of them.

User avatar
RonH
SilverLounger
Posts: 1802
Joined: 02 Mar 2010, 16:53
Location: An Aussie in Norway

Re: Formula assembly

Post by RonH »

You guessed correctly, Argus. English for me, though using English language on a Norwegian pc has its challenges. :innocent:
CYa Ron
W11 pc, Android toys.
The only reason we have the 4th dimension of Time is so that everything does not happen at once.

User avatar
RonH
SilverLounger
Posts: 1802
Joined: 02 Mar 2010, 16:53
Location: An Aussie in Norway

Re: Formula assembly

Post by RonH »

Meant to post, Hans, that for the additions of eg column D10 E G H (missing entry in F) I had to use a + sign for the columns needed. It was the only way I could avoid adding F into the total.
Using , or : did not eliminate F.

Maybe because I still use Excel 2010 but a straightforward calc using +
CYa Ron
W11 pc, Android toys.
The only reason we have the 4th dimension of Time is so that everything does not happen at once.