1. 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

2. 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.

3. 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

4. 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?

5. 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>

6. 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.

7. 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
•