# partial text match in an array summing the total value

1. ## 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  Register To Reply

2. ## Re: partial text match in an array summing the total value

See post #3  Register To Reply

3. ## Re: partial text match in an array summing the total value

Try

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

4. ## Re: partial text match in an array summing the total value

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

Thank you very much
Best  Register To Reply

5. ## Re: partial text match in an array summing the total value

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.   Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 