wild card in sumproduct

[BNT1 via OfficeKB.com]

Hi

Trying to count two crieria using sumproduct. First criteria is in A3, the
second criteria on the indirect sheet is L/R UK, or L/R 104 Minor Uk, or L/R
Spain. or L/R Major Mkts. I want to count all of these, but not Landrover -
ferry, so thought the wild card would be the way forward.

I am using
=sumproduct((indirect(""&F1&"!B1:B2000")=A3)*(indirect(""&F1&"!F1:
F2000="L/R************))-E3

am getting ref#

Is this the best way forward (wild card) and any idea's why it doesnt work

Help appriciated
Regards
Brian

 

[T. Valko]

SUMPRODUCT itself won't accept wildcards.

This might work. It will count all entries in indirect(""&F1&"!F1:F2000")
that start with L/R.

=sumproduct((indirect(""&F1&"!B1:B2000")=A3)*(left(indirect(""&F1&"!F1:
F2000"),3)="L/R"))-E3

 

[BNT1 via OfficeKB.com]

Thank you very much Mr T. It worked perfectly.

This is another piece of the jig-saw and just what i required

Thanks again

regards

SUMPRODUCT itself won't accept wildcards.

This might work. It will count all entries in indirect(""&F1&"!F1:F2000")
that start with L/R.

=sumproduct((indirect(""&F1&"!B1:B2000")=A3)*(left(indirect(""&F1&"!F1:
F2000"),3)="L/R"))-E3

Hi

[quoted text clipped - 17 lines]

Regards
Brian

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Thank you very much Mr T. It worked perfectly.

This is another piece of the jig-saw and just what i required

Thanks again

regards

SUMPRODUCT itself won't accept wildcards.

This might work. It will count all entries in indirect(""&F1&"!F1:F2000")
that start with L/R.

=sumproduct((indirect(""&F1&"!B1:B2000")=A3)*(left(indirect(""&F1&"!F1:
F2000"),3)="L/R"))-E3

Hi

[quoted text clipped - 17 lines]

Regards
Brian