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
Bookmarks