# 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

See post #3

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

Try

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

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

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.

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