Please Correct the Formula

[srizki]

=SUMIF(INDIRECT(C1&"!$AE:$AE"),$A7,INDIRECT(C1&"!$Y:$Y"))

thanks

 

[Aladin Akyurek]

Thank you Aladin,

When I put this formula,

'=SUMPRODUCT((INDIRECT("'"&C1&"'!$A1:$A14919")=A2)*(INDIRECT("'"&C1&"'!$AD1:$AD14919)=B2)*(INDIRECT("'"&C1&"'!$Y1:$y)14919))

it says "The formula you typed contains an error"

and highlights the last quotes.

Thanks

Either:

=SUMPRODUCT((INDIRECT("'"&C1&"'!A:A")=A2)*(INDIRECT("'"&C1&"'!AD:AD")=B2)*(INDIRECT("'"&C1&"'!Y:Y")))

Or:

=SUMPRODUCT(--(INDIRECT("'"&C1&"'!A:A")=A2),--(INDIRECT("'"&C1&"'!AD:AD")=B2),INDIRECT("'"&C1&"'!Y:Y"))

will work on Excel 2007 and later.

Othwerwise, you need to replace the whole column references.

 

[srizki]

Thank you Aladin,

I use 2007 at home, but at work I am using 2003. So I need to use specific range column reference?
You replied at 3:50am.
sahur.
Thanks

Sohail

 

[Aladin Akyurek]

Thank you Aladin,

I use 2007 at home, but at work I am using 2003. So I need to use specific range column reference?
You replied at 3:50am.
sahur.
Thanks

Sohail

2007:

=SUMPRODUCT(--(INDIRECT("'"&C1&"'!A:A")=A2),--(INDIRECT("'"&C1&"'!AD:AD")=B2),INDIRECT("'"&C1&"'!Y:Y"))

Both 2003 and 2007:

=SUMPRODUCT(--(INDIRECT("'"&C1&"'!A2:A1000")=A2),--(INDIRECT("'"&C1&"'!AD2:AD1000")=B2),INDIRECT("'"&C1&"'!Y2:Y1000"))

Adjust the ranges to suit.

 

[srizki]

Thanks you very much Aladin,

JAk

Sohail Rizki

 

[Aladin Akyurek]

Thanks you very much Aladin,

JAk

Sohail Rizki

You are welcome. Thanks for providing feedback.