SumProduct + Text + Indirect = Not working together! At least for me!

[CraigsWorld]

Hi there,

I am using a formula I found that counts the number of ocourances that contain a specific month and year.

Please Login or Register  to view this content.

this works perfectly and gives me the correct result of 2.

What I am trying to do is replace the sheet reference with an Indirect reference, 14843 is cell A5. Here is what I have tried...

Please Login or Register  to view this content.

I get an #REF error and I am not sure what I am doing wrong.

Any ideas?

Thanks
CraigWorld

[Glenn Kennedy]

It would be easier to follow if you had posted a sheet, but...

How does this look:

Please Login or Register  to view this content.

[CraigsWorld]

Ah... Thanks Glenn

Missed the extra quotation mark that I added... funny how the eyes miss little things like that.

Please Login or Register  to view this content.

Thanks Again!

CraigsWorld

[Glenn Kennedy]

It wasn't an extra... it was in the wrong place. it should be after $c$50. But yes, the syntax of INDIRECT does my head in...

[CraigsWorld]

Thanks Again!... I think I understand better now. With indirect, everything needs to be within quotes unless you are using a value from inside a cell. Which is the purpose of indirect. It is definitely confusing but powerful.

Seems a little clearer now...

CraigsWorld

[Tony Valko]

=SUMPRODUCT(--(TEXT(INDIRECT("'"&$A5&"'!$C$5:$C$50"),"yyyymmmm")=Data!$M$2&Data!$L$2))

Quoted references inside the INDIRECT function are automatically evaluated as absolute references so there's no need for the $ signs.

=SUMPRODUCT(--(TEXT(INDIRECT("'"&$A5&"'!C5:C50"),"yyyymmmm")=Data!$M$2&Data!$L$2))