Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum based on color (Excel 2003 )

    Hi All

    I think I saw this before but is unable to find it. I have a column where the numbers for negative numbers are red, positive are
    black. How can I sum column values that are black.

    thanks in advance

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sum based on color (Excel 2003 )

    You can use the SUMIF function, for example:
    <code>
    =SUMIF(A1:A100,">0")</code>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sum based on color (Excel 2003 )

    You may have seen a reference to Chip Pearson's Color Functions In Excel in this forum, but that probably won't help in your situation, because the red and black haven't been set explicitly, but in the number format.

  4. #4
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum based on color (Excel 2003 )

    Hi Hans

    Thank for the formula and for the link . it does help in other tasks.

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum based on color (Excel 2003 )

    Now there is one that Excel 2007 can do. Hmmm...Maybe it does have a few perks after all.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sum based on color (Excel 2003 )

    There are some useful new features in Excel 2007, such as the COUNTIFS and SUMIFS functions that allow multiple criteria.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum based on color (Excel 2003 )

    Hans,

    Yes, I was just reading about those today on a website. There are about 8 or so new functions, including the ones you mentioned. Had always used the Conditional Sum Wizard, but will have to give these new ones Countifs and Sumifs a try.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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