Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SumIf problem (excel 2000)

    We have a spreadsheet which has a number of columns Jan to December the final column lists a number of different prices (range name
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIf problem (excel 2000)

    Your sample data is in A1:E5 including the labels.

    {0,"Jan","Feb","Mar","prices";
    "Green","a","b","d",26;
    "Blue","v","c",0,25;
    "Brown","c","a","d",150;
    0,0,0,"d",260}

    where a 0 indicates an empty cell.

    In B6 enter: =SUMPRODUCT((LEN(B2:B5)>0)+0,prices) [ where 'prices" is the name of the range E2:E5]

    Copy this formula across as far as needed.
    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIf problem (excel 2000)

    Try this array formula:

    =SUM(IF(NOT(ISBLANK(B2:B5)),prices))

    use Ctrl-Shift-Enter to enter it as an array formula

    I attached a copy of your workbook with the new formula.

    Ken
    Attached Files Attached Files

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SumIf problem (excel 2000)

    Any of the following SUMIF's work for cell B6
    <pre>=SUMIF(B2:B5,"<>",prices)
    =SUMIF(B2:B5,"<>"&"",prices)
    =SUM(prices)-SUMIF(B2:B5,"",prices)</pre>

    However, my first choice =SUMIF(B2:B5,"<>""",prices) does NOT work. What's up with that?
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIf problem (excel 2000)

    Your first choice formula does not work because it is missing a couple of quotes. To insert a quote character in a string you have to use two consecutive quotes. So, to be correct, your formula would need to be:

    <pre> =SUMIF(B2:B5,"<>""""",prices)
    </pre>


    However, that also does not work and I think that is a bug in Excel.

    My favorite to do this would be the formula below array entered:

    <pre>=SUM((B2:B5<>"")*prices)
    </pre>

    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIf problem (excel 2000)

    See for the "<>" and "<>""""" in the context of COUNTIF:

    http://www.mrexcel.com/wwwboard/messages/15057.html

    By the way, I'd suggest Roberta to use the cheapest formula which is:

    =SUMIF(B2:B5,"<>",prices)

    if there is no formula returned blanks in the relevant ranges.

    By precaution, I suggested a SUMPRODUCT formula with LEN in order to cope with the possible occurrence of formula returned blanks. Even then, your

    =SUM(prices)-SUMIF(B2:B5,"",prices)

    should be preferred.
    Microsoft MVP - Excel

  7. #7
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIf problem (excel 2000)

    If you're trying to add up prices which correspond to values not equal to a single quote, then you need this:
    =SUMIF(B2:B5,"<>""",prices) which works fine.
    If you define a name, Quote, to be ="""", then use
    =SUMIF(B2:B5,"<>"&quote,prices) it'll also work. If you highlight from the first quote to the "e" in Quote and press F9, you'll see "<>""". I don't see a problem here, nor do I see a bug. Using =SUMIF(B2:B5,"<>""""",prices) is comparing B2:B5 to 2 quotes, not one.

Posting Permissions

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