Solved: SUMPRODUCT WITH COLUMN()

[asingh]

Hi,

I have the following formula and its working perfect:

=SUMPRODUCT((INDIRECT(Rng_Sat&Rng_Count)>=$N$7)*(INDIRECT(Rng_Sat&Rng_Count )<=$O$7)*(INDIRECT(Rng_Mod&Rng_Count)=INDIRECT(ADDRESS(54,3)))*1)

Now, instead of the "3" in the last address function, I want to use column(). So the formula now becomes"

=SUMPRODUCT((INDIRECT(Rng_Sat&Rng_Count)>=$N$7)*(INDIRECT(Rng_Sat&Rng_Count )<=$O$7)*(INDIRECT(Rng_Mod&Rng_Count)=INDIRECT(ADDRESS(54,COLUMN())))*1)

I am getting a #VALUE....!

And instead of INDIRECT(ADDRESS(54,COLUMN())), which returns $C$54, I hard code the $C$54, the formula works...I am standing at the third column as I type this formula in...why is the column() assignment not working..my brackets too seem fine. Basically I want to write the forumala such..when I drag it horizontally, the column() will re-index and the formula will not need to be changed....


thanks and regards,

asingh

[mdmackillop]

I've not tried this within your formula, just by testing the problem part, but try
=INDIRECT(INDIRECT(ADDRESS(54,COLUMN())))

[Bob Phillips]

If I am reading your formula correctly, you don't need the *1 at the end (totally superfluous), and you don't even need SUMPRODUCT, -- will do just fine

=--((INDIRECT(Rng_Sat&Rng_Count)>=$N$7)*
(INDIRECT(Rng_Sat&Rng_Count )<=$O$7)*
(INDIRECT(Rng_Mod&Rng_Count)=INDIRECT(ADDRESS(54,3)))*1)

The problem with adding COLUMN() in there is that it changes the ADDRESS function from returning a single cell address to an array of cell addresses (it is an array, even though there is just one value), so you need to process the array before passing it to the INDIRECT. You can do that with the T function

=--((INDIRECT(Rng_Sat&Rng_Count)>=$N$7)*
(INDIRECT(Rng_Sat&Rng_Count )<=$O$7)*
(INDIRECT(Rng_Mod&Rng_Count)=INDIRECT(T(ADDRESS(54,COLUMN())))))

[asingh]

Hi,

XLD, I tried what you had suggested. I removed the SUMPRODUCT and used the "--" operator. As well as used the T function to get the literal text value. Now my counts are showing up as zero.....! Also removed the *1.

Not sure what is happening...

regards,

Asingh

[mdmackillop]

and Post #2?

[Bob Phillips]

Are you sure the value being tested is in that column? A vaguary of using that approach.

Post the workbook, or at least layout the data.

[Bob Phillips]

and Post #2?

Won't make any differnece in his example, because SP will treat INDIRECT(INDIRECT(ADDRESS(54,COLUMN()))) as an array, so you will still need to take an apoproach like mine.

[mdmackillop]

Fair enough. I'll leave this one to the Expert.

[asingh]

Hi,

I am attaching a basic version of the sheet. Column C is being evaluated for the value which is in E12. With a normal SUMPRODUCT --- hardcoded with the address reference for E12 I get the correct count of 5. When I try it with Column() using "--" or SUMPRODUCT I get the #VALUE Errors. I have also shown how the address reference is built using column(),address and Indirect. In both the formulas I used "T" operator to get the true value of the address array from COLUMN().

Am not being able to replicate the Zero count error..which I mentioned before...

The names ranges are there too...!

NOTE:
MD I did try out what you had mentioned...but that did not work. Apologies for not mentioning that in the previous threads..thanks for trying though.

thanks a lot for all your valuable inputs...

regards,
asing

[Bob Phillips]

asing, I was wrong in my assumption that you were indirecting to a single cell, so try this slight variation

=SUMPRODUCT(--(INDIRECT(Rng_Eval&Rng_Count)=T(INDIRECT(ADDRESS(12,COLUMN())))))

[asingh]

Hi,

It works perfect......I tried it on my modeling sheet..and getting exact counts....thanks a lot...!

Marking this as solved.

regards,

asingh