+ Reply to Thread
Results 1 to 13 of 13

Summing columns

  1. #1
    Registered User
    Join Date
    01-17-2007
    MS-Off Ver
    MS Office 365
    Posts
    71

    Summing columns

    Hello all

    I would like help in summing up totals in multiple columns based of the colour of the columns, please see attached spreadsheet.

    Total the value in grey, brown and yellow columns but limited to the yellow column, the great value in ether grey column and the greater 2 values in the brown with the total showing in column E.

    Also the number of events that the above totals points to show in column F as shown in E5 & F5 (manually entered)

    Thank you in advance.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,801

    Re: Summing columns

    Excel formulas cannot "see" colors, colors are formatting, not data, and formulas work on data.

    However, all is not lost, it looks like each color is a certain value? If so, you could probably use SUMIF/S.

    Can you show me what a sample answer would look like, and where you want it to be?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-17-2007
    MS-Off Ver
    MS Office 365
    Posts
    71

    Re: Summing columns

    Thank you for your reply.

    The row total to show in col E by added the following values Col p plus the greater of col H & X plus the greater 2 values from Col J, L, N, R, T & V.

    In col F to show the number of values to get the total in col E.

    The manual calculations are showing in the red cells.

    Does this simplify things?
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,801

    Re: Summing columns

    This is messy, but it does what you want...
    =SUM(P7,MAX(H7,X7),LARGE((J7,L7,N7,R7,T7,V7),1),LARGE((J7,L7,N7,R7,T7,V7),2))

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,626

    Re: Summing columns

    Similar to Ford's.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and for the counts in column F.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    which can be shortened I'm sure.
    Dave

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,626

    Re: Summing columns

    For the second formula this is a little shorter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,801

    Re: Summing columns

    Thanks Dave, I keep forgetting about the {1,2} option

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,626

    Re: Summing columns

    You're welcome. Yeah it's easy to overlook. Surprised I thought of it. LOL

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,801

    Re: Summing columns

    Just curious, why did you bring in the >0 part?

  10. #10
    Registered User
    Join Date
    01-17-2007
    MS-Off Ver
    MS Office 365
    Posts
    71

    Re: Summing columns

    Thanks guys.

    Like most things there are many ways to get the answer.

    I will set the post as solved.

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,626

    Re: Summing columns

    @Ford
    Just curious, why did you bring in the >0 part?
    To provoke the 1s so they could be summed. My concern was some of the numbers are 0s, and the COUNT(1/() stuff just seemed awkward.

    =COUNT(1/P7)+COUNT(1/MAX(H7,X7))+COUNT(1/LARGE((J7,L7,N7,R7,T7,V7),{1,2}))

    Couldn't come up with a COUNTIFS or FREQUENCY approach either.

    Open to ideas. You see something?

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,626

    Re: Summing columns

    You're welcome. Thanks for the feedback.

    You're of course right about the "many ways to get the answer".

    Kids ... candy store. LOL

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,801

    Re: Summing columns

    Happy to help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Summing up columns by Name
    By monika.canon in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-18-2017, 06:21 PM
  2. Replies: 2
    Last Post: 06-03-2015, 12:53 PM
  3. Summing two columns in VBA
    By rogerpops in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-29-2014, 09:11 PM
  4. Summing columns
    By nonexcelman in forum Excel General
    Replies: 6
    Last Post: 08-24-2010, 03:26 PM
  5. Summing Columns-revolves around summing particular items
    By savv32 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-06-2005, 09:05 PM
  6. Summing Columns-How do I add non-numbered columns in excel?
    By Highlander in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-28-2005, 12:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1