Autosum forumula

Topic

New Reply

Is there a formula in MS Excel 2003 that says the following: give me the sum of all of the numbers in Column D if there is a checkmark (or an “x”) next to that number in Column E?

Best regards,

JMT

Reply | Quote

Replies

Both SUMIF and SUMPRODUCT worksheet functions can achieve this:

=SUMIF(E1:E5,”x”,D1:D5)

OR

=SUMPRODUCT((D1:D5)*(E1:E5=”x”))

(adjusting ranges accordingly)

NB. make sure that both ranges span an equal number of cells

snicho

Reply | Quote

Thank you. Is there also a way to count only the D cells when any of the cells in the F column contains any number?

Also, is it possible to split the E1 cell into two rows without splitting any of the other cells in the E column or 1 row?

Reply | Quote

Thank you. Is there also a way to count only the D cells when any of the cells in the F column contains any number?

Your counting should be achieved in the same way

=SUMPRODUCT(1*(ISNUMBER(F1:F5)))

Also, is it possible to split the E1 cell into two rows without splitting any of the other cells in the E column or 1 row?

Not sure what you mean by this.
I think the Answer is possibly NO.
What is in E1 and how do you want to Split the cell into 2 rows.

Manually, you can use ALT ENTER to force a new line in a cell,
or turn on Text Wrap.
In a Formula you can use CHAR(10) e.g. “=”X” & CHAR(10) & “Y” as long as Wrap Text is on the Column E
But the whole of Row 1 will increase in height.
The only other alternative would be Inserting a New Row 1
then merging All Cells in Rows 1 and 2 except E1 across 2 rows
Up to the point you want to Print

Not sure that answers your question though.

Reply | Quote

Left alt + enter closes the worksheet; right alt + enter does nothing.

I have a cell that I want to split a cell into two rows: the top row will say “Paid for by:” and the second row will be split into 4 columns, each of which contains the name of a person. I don’t want any of the other cells in that row to be split, but I want all of the cells in that column also split into four, so that I could put a check under the name of the person who paid for a specific item.
Can this be done?

Reply | Quote

Hello – Exactly what does cell E1contain? What would you like in each of the 4 columns?
I am thinking Data, Text to Columns might work.

Reply | Quote

[alt] [enter] is only valid in Enter Mode of a cell.
Outside of cell edit it has another function.

Can you draw what you are after in Word and post it as a picture.
It sounds like you can do what you want by using merge cells.
What you CANNOT do in excel is to SPLIT a cell like you can in a Word Table

Reply | Quote

Hello – Take a look at the attached file. Are you looking for something like this? You can have as few or as many names in Column E as you need. This uses Data/Text to Columns.

Tim

Reply | Quote

AKW: that’s what I want. How did you merge A1-D1?

Reply | Quote