Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    sumif a particular format is present (2000)

    I have a column of data in column B. Corresponding to the data in column B are dates in column A. What I would like to do is sum all data that have their corresponding dates (i. e., the corresponding cells in column A) shaded a particular color, say yellow. Any ideas?
    Thanks.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumif a particular format is present (2000)

    Here is one way.

    Lets assume your dates are in A2:A100
    lets asume column D is empty
    - Select cell D2
    - Choose Insert, name, define from the menu
    - Enter this name: GetColor
    - And this formula:

    =GET.CELL(63,Sheet1!A2)+0*NOW()

    Make sure there are NO dollar signs!
    - OK your way out.
    - Now type this formula into cell D2:
    =GetColor
    - Drag down to match the rows in column A
    - Now use SUMIF to sum the dates of a particular color in column D (or whatever you used):

    This sums al data in column B with yellow dates in column A:

    =SUMIF(D2100,"=6",B2:B100)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: sumif a particular format is present (2000)

    Thanks, Jan, it works great! BTW, where might I find a list of shading codes in case I encounter/use another color?
    Thanks again.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumif a particular format is present (2000)

    The easiest is to just shade the first cell with a couple of different colors and note the numbers shown in the formula cell.
    Note this trick does not recalc automatically when you change the shading color, you need to press F9 for that.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: sumif a particular format is present (2000)

    thanks again

Posting Permissions

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