wild card in sumproduct

  • Thread starter Thread starter BNT1 via OfficeKB.com
  • Start date Start date
B

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
 
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
 
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 said:
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
Click to expand...
[quoted text clipped - 17 lines]
Regards
Brian
Click to expand...
Click to expand...
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


BNT1 via OfficeKB.com said:
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 said:
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
Click to expand...
[quoted text clipped - 17 lines]
Regards
Brian
Click to expand...
Click to expand...
Click to expand...
 
Back
Top