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

 

[AndyJR]

Hi Konew1,

this formula did the Job, but it make me think and analyze a few things.. is volatile, what means Q1, Q36, 101,5 and other numbers that have nothing to do with my ranges?... if volatile means that in any moment it might change the rows/ column value (or not?) or when will change?

wow. i tough that excel was easier... Im so sorry, My respect on your knowledge guys!

Andy

 

[AndyJR]

Hi Konew1,

The approach that you mention is going to have the CELL REFERENCE AT ALL TIME??????
Just asking...


Andy

 

[konew1]

Follow the link posted in one of the earlier replies to your thread for info on volatile functions. Basically it means that the system will run a bit more slowly because there are extra cell recalculations.

The formula will select the same range Q37:U137 even if you insert more rows above 37, so long as you don't inset a new row 1.

The formula refers to 36 (not Q36) so it finds the cell 36 down from Q1, will always be Q37, and then selects a range of 101 rows down and 5 columns wide. that is Q37:U137 then the same to get range $K37:$O137

 

[AndyJR]

Hi Konew1 and Marcelo Branco.

Understood the SUMPRODUCT with OFFSET.

Thank you so much, and Big thanks to Marcelo branco for the help !!!!!!!!!!!

Regards
AndyJr