Spreadsheet errors

[mdmackillop]

The attached file contains values from a copy/paste special from a very large workbook I've been putting together over the past 2 weeks. When I went to do some analysis, I came up with some very peculiar answers to a simple SumIf formula. The data in the book involves creating then importing data from 300+ csv files using another programme, so I really don't want to do this again. The data seems consistent in some ways, but I'm now very distrustful of it. Any suggestions as to how this might be "repaired"?

[Bob Phillips]

The attached file contains values from a copy/paste special from a very large workbook I've been putting together over the past 2 weeks. When I went to do some analysis, I came up with some very peculiar answers to a simple SumIf formula. The data in the book involves creating then importing data from 300+ csv files using another programme, so I really don't want to do this again. The data seems consistent in some ways, but I'm now very distrustful of it. Any suggestions as to how this might be "repaired"?

Malcolm,

I think the problem is caused by unequal ranges. If you change say AX12 to

SUMIF($C1:$AT1,AX$4,$C12:$AT12)

you get a 'better' answer.

I have come acroos this before, but I cannot remember the context, or the rationale, but I will try and find it tomorrow.

[mdmackillop]

Thanks Bob,
I'm sure I've used unequal ranges before, but never noticed a problem. I'll be more careful in future!
Regards
Malcolm

[Bob Phillips]

Thanks Bob,
I'm sure I've used unequal ranges before, but never noticed a problem. I'll be more careful in future!
Regards
Malcolm

Now here's interesting! If you use equal ranges, it is a non-v9olatile function, if you use unequal ranges, it becomes volatile. Need to investigate this tomorrow.

[Brandtrock]

Why not use:

[VBA] =SUMPRODUCT(((($C$1:$AT$1)=AY$4)*1),($C12:$AT12))[/VBA]

[Bob Phillips]

Why not use:

[VBA] =SUMPRODUCT(((($C$1:$AT$1)=AY$4)*1),($C12:$AT12))[/VBA]

Why? What is wrong with SUMIF? SUMPRODUCT will also bomb if you use unequal ranges.

[mdmackillop]

Why not use:

[VBA] =SUMPRODUCT(((($C$1:$AT$1)=AY$4)*1),($C12:$AT12))[/VBA]

I have enough problem explaining SumIf to my colleagues, I'm not sure if I have the patience for SumProduct.

[Brandtrock]

Why? What is wrong with SUMIF? SUMPRODUCT will also bomb if you use unequal ranges.

Bob, there's nothing wrong with SUMIF, except that Malcolm wasn't getting the results he expected.

I have enough problem explaining SumIf to my colleagues, I'm not sure if I have the patience for SumProduct.

I understand completely. No sense any more than upper management requires.

[mdmackillop]

Hi Brandtrock & Bob
With the given insight, the simplest solution appears to be in AX6, =SUMIF($1:$1,AX$4,6:6), which I guess I ignored because it looks like it would produce a circular reference.
Regards
MD

[Bob Phillips]

Bob, there's nothing wrong with SUMIF, except that Malcolm wasn't getting the results he expected.

That was because he used incorrect syntax, not because of SUMIF. As I said, if the same error had been used with SUMPRODUCT, that would also bomb.

[Brandtrock]

That was because he used incorrect syntax, not because of SUMIF. As I said, if the same error had been used with SUMPRODUCT, that would also bomb.

Bob,

I hope this doesn't appear to be picking nits, but SUMIF doesn't return a #VALUE error when the ranges are of different size as SUMPRODUCT will. This is, the overt indication of bombing IMHO.

You, of course, are right that unequal ranges can produce bad results with SUMIF. See the attached file if my explanation here is not clear for anyone reading through.

Using an entire row/column in the SUMIF as the criteria range and a small results range of say, 6 cells, will return the expected answer if the ranges begin in the same row/column. If they don't, then the return is FUBAR as the result range sums the xth element. For instance, if the 3rd, 4th, 5th, and 6th element meet the criteria and the result range is only four elements long, the SUMIF returns the sum of elements 3 and 4.

I'm in kind of a rush as my 4 year old is trying to check out the entire video collection here at the library.

Gotta run for now though.