I have the following formula
={SUMPRODUCT((ISNUMBER(SEARCH(A1,'Mar Subm'!$G$2:$G$3000)))*('Mar Subm'!$B$2:$B$3000)*IF(RIGHT('Mar Subm'!$C$2:$C$3000,1)="M",12,1))}
Col G contains unique refereneces i.e. 123ABC, Col B has a premium amount and Col C has an M or Y for payment frequency.
It is used to recall the amount of annualised premium (multiplied by 12 if 'M') based on what the user types in cell A1.
The user should type something like 123ABC in A1 to return data for that unique record. I have entered my formula in such a way that if A1 is blank then it gives a total figure for everything.
I am having a problem though if the user just types a number in (e.g. if they type a 3 in then they get all records with a 3 in).
Does anyone have a solution that would work so that it only returns when the whole string is matched not part?
I know I could just limit the user to select certain unique 123ABCs but I will shortly be using this formula on company names and would run into trouble if I had company names like "The Dog", "The Big Dog", "The Dog Big" or "The Big Dog Company" as it could return a lot more than I bargained for if I typed say "The Dog" in.
Many Thanks
Bookmarks