Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Aug 2006
    Posts
    784
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    New Lounger
    Join Date
    Dec 2009
    Location
    Melbourne
    Posts
    7
    Thanks
    0
    Thanked 1 Time in 1 Post
    Both SUMIF and SUMPRODUCT worksheet functions can achieve this:

    =SUMIF(E1:E5,"x",D15)

    OR

    =SUMPRODUCT((D15)*(E1:E5="x"))

    (adjusting ranges accordingly)

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

    snicho

  3. #3
    5 Star Lounger
    Join Date
    Aug 2006
    Posts
    784
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by jmt356 View Post
    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)))

    Quote Originally Posted by jmt356 View Post
    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.
    Andrew

  5. #5
    5 Star Lounger
    Join Date
    Aug 2006
    Posts
    784
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  6. #6
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    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.

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [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

    [attachment=88684:split2.jpg]
    Attached Images Attached Images
    Andrew

  8. #8
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    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



    [attachment=88685:Parse a cell.xls]
    Attached Files Attached Files

  9. #9
    5 Star Lounger
    Join Date
    Aug 2006
    Posts
    784
    Thanks
    0
    Thanked 0 Times in 0 Posts
    AKW: that's what I want. How did you merge A1-D1?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •