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)*(indi rect(""&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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200711/1

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


--
Biff
Microsoft Excel MVP


"BNT1 via OfficeKB.com" <u19326@uwe wrote in message
news:7bb629a094ad8@uwe...
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)*(indi rect(""&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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200711/1

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

T. Valko wrote:
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)*(lef t(indirect(""&F1&"!F1:
F2000"),3)="L/R"))-E3

Hi

[quoted text clipped - 17 lines]
Regards
Brian

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200711/1

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"BNT1 via OfficeKB.com" <u19326@uwe wrote in message
news:7bc30406afb11@uwe...
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

T. Valko wrote:
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)*(le ft(indirect(""&F1&"!F1:
F2000"),3)="L/R"))-E3

Hi

[quoted text clipped - 17 lines]
Regards
Brian

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200711/1