PDA

View Full Version : Solved: SUMPRODUCT WITH COLUMN()



asingh
04-08-2007, 09:15 PM
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
04-09-2007, 01:51 AM
I've not tried this within your formula, just by testing the problem part, but try
=INDIRECT(INDIRECT(ADDRESS(54,COLUMN())))

Bob Phillips
04-09-2007, 02:34 AM
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
04-09-2007, 08:17 AM
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
04-09-2007, 08:30 AM
and Post #2?

Bob Phillips
04-09-2007, 08:51 AM
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
04-09-2007, 08:54 AM
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
04-09-2007, 08:57 AM
Fair enough. I'll leave this one to the Expert.

asingh
04-09-2007, 04:14 PM
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
04-09-2007, 04:26 PM
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
04-09-2007, 05:56 PM
Hi,

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

Marking this as solved. :)

regards,

asingh