wild card in SUMPRODUCT formula

  • Thread starter Thread starter Tmt
  • Start date Start date
T

Tmt

Hello,

This SUMPRODUCT won't work with the wildcard * when I tried to count all the
70222A, B, C, D and E under the "calibrated" condition. Please help.

=SUMPRODUCT(('Q3'!E12:E153="Calibrated")*('Q3'!G12:G153="70222*"))
 
Possible alternative:

=SUMPRODUCT(('Q3'!E12:E153="Calibrated")*(ISNUMBER(SEARCH("70222*",'Q3'!G12:G153))))
 
Tmt said:
=SUMPRODUCT(('Q3'!E12:E153="Calibrated")*('Q3'!G12:G153="70222*"))
Click to expand...


Try...

=SUMPRODUCT(--('Q3'!E12:E153="Calibrated"),--(LEFT('Q3'!G12:G153,5)="7022
2"))
 
Try

=SUMPRODUCT(('Q3'!E12:E153="Calibrated")*(LEFT('Q3'!G12:G153,5)="70222"))

If this post helps click Yes
 
correction
=SUMPRODUCT(('Q3'!E12:E153="Calibrated")*('Q3'!G12:G153=70222&{"A","B","C","D","E"}))
 
Well, if you had checked your post on 28th August you would have had
your answer 3 days ago.

Pete
 
Back
Top