SumIf problem (excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

Any of the following SUMIF’s work for cell B6

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

However, my first choice =SUMIF(B2:B5,””””,prices) does NOT work. What’s up with that?

Reply | Quote

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:

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

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:

=SUM((B2:B5"")*prices)

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote