Excel 2003 - SUMIF

[Craig Brandt]

I have a table where the rows of specific account data are intertwined with
result data. One of the columns has account numbers (9 characters in length)
for those rows containing account data and the non-account specific rows
have other data but never is the data 9 characters in length.

My thought was to do a SUMIF at the bottom of the table, summing only those
rows that have account data, ignoring the other rows. Is there a way to
write the SUMIF formula such that it will only pull the rows where the
Account number column contains a 9 character entry?

Craig

 

[AltaEgo]

Use an array formula

Then following checks that the length of the values is B1:B6 is 9 characters
long. In each case when it is, it sums C1:C6 values

=SUM(IF(LEN(B1:B6)=9,C1:C6))

To enter an array formula, you need to press Ctrl/Shift/Enter when when you
complete your formula in the formula bar.

 

[Bob Phillips]

Non-array

=SUMPRODUCT(--(LEN(B1:B6)=9),C1:C6)

 

[AltaEgo]

Bob

Until I analysed you response below, my presumption was that a double
negative is a positive leading to the conclusion that
=SUMPRODUCT(LEN(B1:B6)=9,C1:C6) should do the same thing as below. As you
would know, it does not.

Can you explain how the double minus works in the formula. In particular,
does it have wider application?

 

[AltaEgo]

I just had the Eureka moment. The double minus just changes the True or
False to 1 or zero so the True False part of the equation can be evaluated
as a number array.

 

[Craig Brandt]

Thanks for your help. I integrated the Array solution and it did the
trick. That is, until I tried to clean up and format the cells when I got
the message that I cannot chage an element of an array.
I then saw the SUMPRODUCT formula and integrated it and it solved my
problem.

The spread sheet is one of those that gets a lot of manual manipulation,
therefore the array solution would interfer with some of the changes that
would be made during daily use.

Thanks again,

Craig

 

[AltaEgo]

Thank you for the feedback. What version Excel are you using ... 97?

 

[AltaEgo]

Or were you changing the size of the array by inserting or deleting a row?

 

[Craig Brandt]

Steve:
I highlighted a coulmn, copied the format of that column and then tried to
format another column used that data. The array formulas wouldn't allow it.

I am using Excel 2003. I would love to go to Office 2007 as it solves a
bunch of problems, but my system and OS are too old to support it.

Craig

 

[AltaEgo]

That's intriguing. I am using 2003 but was unable to duplicate the "cannot
change an element of an array" problem no matter how I copied and pasted
formatting nor from where or to where. Having said that, for no apparent
reason Excel is currently allowing me to insert or delete columns or rows
that cut through the array where it was not doing the same yesterday
(triple-checked it was the same array formula). Each time, the array formula
updates as would regular formula.

The only thing that makes sense is pressing F5 and selecting Special/Current
Array does not result in selecting the array. But, it was not selecting the
array yesterday either at the time Excel was preventing insert of row or
column!

Perhaps one of the experts can explain this changing error behaviour to a
mystified highly-experienced user?