Column() Function Leading To #VALUE! Error

[3LeggedDog]

Unfortunately, I'm at my work computer at the moment, and am not able to copy the sheet over, but I will try to describe the issue here.

At work, I use Excel 2003. I have a formula that essentially mimics the 2007 SumIfs function, while calling a range from a different sheet. In order to make the sheet easily scaleable, I tried to use the column() function, which I've used many times without incident. When I just put in the specific column that I want, the formula works perfectly, but when I replace the column number with the column() function, I get a #VALUE! error.

I ran the Formula Evaluator to determine where the issue was coming up - for some reason, the column() call is evaluating to a {3} (or 4, 5, 6, etc depending on the column), rather than a regular 3. I've never seen a formula evaluate out this way, with braces, and I'm not sure what could be causing that. Here's the entire formula:

=SUMPRODUCT(INDIRECT(CONCATENATE("Sheet1!",ADDRESS(5,MATCH(INDIRECT(ADDRESS(1,COLUMN())),Sheet1!$A$4:$CC$4,0)),":",ADDRESS(5000,MATCH(INDIRECT(ADDRESS(1,COLUMN())),Sheet1!$A$4:$CC$4,0)))),INDIRECT(CONCATENATE("Sheet1!",ADDRESS(5,MATCH($A3,Sheet1!$A$4:$CC$4,0)),":",ADDRESS(5000,MATCH($A3,Sheet1!$A$4:$CC$4,0)))),INDIRECT(CONCATENATE("Sheet1!",ADDRESS(5,MATCH(INDIRECT(ADDRESS(2,COLUMN())),Sheet1!$A$4:$CC$4,0)),":",ADDRESS(5000,MATCH(INDIRECT(ADDRESS(2,COLUMN())),Sheet1!$A$4:$CC$4,0)))))

The relevant part, though, is here:
=SUMPRODUCT(INDIRECT(CONCATENATE("Sheet1!",ADDRESS(5,MATCH(INDIRECT(ADDRESS(1,COLUMN())),Sheet1!$A$4:$CC$4,0)),":",ADDRESS(5000,MATCH(INDIRECT(ADDRESS(1,COLUMN())),Sheet1!$A$4:$CC$4,0))))

Again, I wish that I could post this using Colo's HTML maker, but I don't have it here at work, and adding things onto the computers at work is probably frowned upon.

Any help would be appreciated - I'm just really puzzled by why the column() call would evaluate in a way that I've never seen before.

Thanks,
3LD

 

[Aladin Akyurek]

Just curious about the SumIfs formula you seem to simulate. Care to post it?

 

[3LeggedDog]

Aladin,
I misspoke (mistyped) earlier when I implied that this particular formula simulates SumIfs - the columns that I'm taking the dot product of with SumProduct have a binary value in each cell. Essentially I'm trying to get the dotproduct of three columns, and two of those columns are just binary.

I will try to post some informative stuff from the sheet itself tomorrow when I'm back at work.

Thank you!