Results 1 to 7 of 7
Thread: SumIf problem (excel 2000)

20020202, 18:32 #1
 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

20020202, 19:00 #2
 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

20020202, 19:15 #3
 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 CtrlShiftEnter to enter it as an array formula
I attached a copy of your workbook with the new formula.
Ken

20020202, 23:37 #4
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
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>

20020203, 00:18 #5
 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

20020203, 08:24 #6
 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

20020204, 12:28 #7
 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,"<>""e,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.