Please Correct the Formula

[srizki]

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

thanks

 

[barry houdini]

Are you getting an error?

If sheet name in C1 has spaces (or is numeric) that won't work, you need

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

 

[Expiry]

What's in C1?

 

[srizki]

Ok let me check it again Barry.
and C1 has tab name that is Month.

 

[srizki]

Are you getting an error?

If sheet name in C1 has spaces (or is numeric) that won't work, you need

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

Aaah,

October in C1 was 10/01/2011 and formated as mmmm.

Thanks Barry and Expiry.

 

[barry houdini]

If you use TEXT function in your formula then C1 could be either text or a formatted date, it won't matter

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

 

[srizki]

Thank you Barry, but what if I want to use SUMPRODUCT, How would I change it.

Thanks a lot.

 

[srizki]

And this is what I am doing that is not working.

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

Thanks

 

[Aladin Akyurek]

And this is what I am doing that is not working.

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

Thanks

What do you get?

 

[srizki]

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