on SUMPRODUCT with multiples criteria How to Nest INDIRECT

[AndyJR]

Hi,

i read and googled about this topic and still i'm not clear yet nesting formula syntax and usage of INDIRECT with some others formulas using multiples range and/o criterias. For example :
I need to have cell reference using INDIRECT with SUMPRODUCT after added my result cell display #REF! Error
and i don't really know where is the mistake.

the problem is that i need to nest this cell reference function to others formula as INDEX/MATCH/ ROW and UNIQUE and I see it more complicated..

please any help??

'my working formula
=SUMPRODUCT(--($Q37:$U137&$K37:$O137=O4&M4))

'the new Formula with INDIRECT display #REF! Error
=SUMPRODUCT(--(INDIRECT("$Q37:$U137")&INDIRECT("$K37:$O137"),INDIRECT("=O4&M4")))

Thanks in advance !

Andy

 

[Marcelo Branco]

Hi Andy

I don't understand why are you using INDIRECT. Could you, please, post a smal data sample and expected result(s).

M.

 

[FDibbins]

Like Marcelo, I am puzzled as to why you want to use INDIRECT() here? INDIRECT() is usually used to convert text into a format that excel can use in a formula - often to include a cell containing a sheet name, into a formula.

What exactly are you trying to do here?

 

[AndyJR]

Hi Marcelo Branco and Hi mr Fdibbins

i can't posted because is huge and is data dependeble (one data depend from other one and so on ).
the formulas are set on TOP in a range (F4 to TV14)
And on BOTTOM, Row C37 is where the data is updated after a macro Insert a new Row on daily basis , so when a new data is input it The cell reference change
e.i SUMPRODUCT(--($Q37:$U137&$K37:$O137=O4&M4))
to
SUMPRODUCT(--($Q38:$U138&$K38:$O138=O4&M4))
The goal is to have a cell reference at all time SUMPRODUCT(--($Q37:$U137&$K37:$O137=O4&M4))

i know it sound weird, because i read that sumproduct with indirect is volatile (i don't know what that mean)
but anyway is posible to add another formula to do the job and keep the cell reference at all time?
perhaps a vba code?

Thanks marcelo for really fast response.


Andy

 

[Marcelo Branco]

If i fully understand what you need, being known the last row with data (certainly there are ways to get it), a *possible* formula would be.

=SUMPRODUCT(--(INDEX(Q:Q,$Z$1-100):INDEX(U:U,$Z$1)&INDEX(K:K,$Z$1-100):INDEX(O:O,$Z$1)=O4&M4))

Where Z1 contains the last row with data (137 in the first case and 138 in the second)

About volatile functions, take a look at
Volatile Excel Functions -Decision Models

M.

 

[AndyJR]

Hi Marcelo,
My formula is " SUMPRODUCT(--($Q37:$U137&$K37:$O137=O4&M4)) "
you are using Q:Q , Then Z Then U:U then K:K and O:O
I'm sorry my naive attitude, but still i don't get it what the Z range have to do with the range that need to be applied
because Z1 contain some help text on my sheet, then how come... you know..
I'm having #VALUE! error

Thanks, and i'm sorry


Andy

 

[Marcelo Branco]

Replace Z1 in the formula by an unused cell, say, AZ1 for example, and put the last row in that cell.

M.

 

[AndyJR]

Hi marcelo,

does this formula is going to keep the same cell reference at all time as need it?, because that is the main goal..
i'm going to try right now...

Thank you !!


Andy

 

[AndyJR]

Hi Marcelo,

Is no way to get any change, due that the Z factor is not pointing the range that i need ("Z$1-100) vs ($Q37:$U137) (&$K37:$O137)
I don't know where or how to insert the Z range/formula tinghi..
i'm sorry, i start to think that the bug is on me, because i don't understand..



Andy

 

[konew1]

Here is another approach, but note that =offset() is also volatile

=SUMPRODUCT(--(OFFSET(Q1,36,,101,5)&OFFSET(K1,36,,101,5)=O4&M4))