Sumproduct, single criteria identified within multiple in single c

M

Monte

I am counting the number of services done by using two letter abreviations,
TN CL PS MV BK RF, Referenced as F74 F75 F76 F77 F78 F79 respectively
I have this formula that works well with a single reference (F74).
=SUMPRODUCT((AL29:AL46=O77)*(AN29:AN46=F74)*(AN29:AN46=F74)).
I type TN in the cell in the array AN29:AN46 and the other two qualifiers
logic true and it directs the answer to be 1 and so on down the column.
I need it to identify a single reference from within multiple references
within a single cell, where (AN29:AN46) could be any or all of the
abbreviations in one cell and still have it identify only one.

=SUMPRODUCT((AL29:AL46=O77)*(AN29:AN46=F74)*(AN29:AN46=F74)).

AL29 AN29
RM TN BK PS
RM TN MV
RM RF

Looking forward to your assistance
 
B

Bob Phillips

Try

=SUMPRODUCT((AL29:AL46=O77)*(AN29:AN46=F74)*(ISNUMBER(SEARCH(F74,AN29:AN46))))
 
M

Monte

=SUMPRODUCT((AL29:AL46=O77)*(ISNUMBER(SEARCH(F74,AN29:AN46)))*(ISNUMBER(SEARCH(F74,AN29:AN46))

Bob, I modified the second logic to have the same as the third and it worked
beautifully:). I can't thank you enough!!!!!, Monte
 
M

Monte

Bob, I started this project working with multiple criteria referencing to
another array. IF=true, If=true, Then do this. I realzed from your question
that I had been reading about the 2 part sumproduct and just never put it
together in my mind. I looked at all my formulas ad was able to trim them
back with good success. Again THANKS!!!, Monte
 
Top