partial text match in an array summing the total value

Hi,

I have a formula which totals up quite a few columns but only does so when a condition is met (AB7)

{=SUM((I23:I4000+J23:J4000+S23:S4000+T23:T4000+U23:U4000+V23:V4000+L23:L4000+N23:N4000+P23:P4000+R23:R4000+K23:K4000+M23:M4000+O23:O4000+Q23:Q4000)*(--(F23:F4000=AB7)))}

It works for exact matches but I want it to be able to do partial text matches for the condition which I can't get to work... I've tried AB7&"*" but this doesn't seem to work. Can you not do partial matches in arrays?

The text that I want it to find is when it has a prefix "PS". so the following fields that contain PS: PS01, PS02, PS03 etc (these are in column F) would have the range I23:V4000 summed. An example of the non working partial text match is below

{=SUM((I23:I4000+J23:J4000+S23:S4000+T23:T4000+U23:U4000+V23:V4000+L23:L4000+N23:N4000+P23:P4000+R23:R4000+K23:K4000+M23:M4000+O23:O4000+Q23:Q4000)*(--(F23:F4000=AB7&"*")))}

Any help would be much appreciated

Try

=SUMPRODUCT((I23:V4000)*(ISNUMBER(SEARCH("PS",F23:F4000))))

Next time I'm stuck I'll do what you suggested... In the meantime that formula worked perfectly!!

Thank you very much
Best

It is always helpful to post a small file (20-30 rows) as what is obvious to the OP (you) may not be those of us trying to find a solution.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.