|
Sumproduct, single criteria identified within multiple in single c
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 -- Daily Tracker |
Sumproduct, single criteria identified within multiple in single c
Try
=SUMPRODUCT((AL29:AL46=O77)*(AN29:AN46=F74)*(ISNUM BER(SEARCH(F74,AN29:AN46)))) -- __________________________________ HTH Bob "Monte" wrote in message ... 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 -- Daily Tracker |
Sumproduct, single criteria identified within multiple in sing
=SUMPRODUCT((AL29:AL46=O77)*(ISNUMBER(SEARCH(F74,A N29: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 -- Daily Tracker "Bob Phillips" wrote: Try =SUMPRODUCT((AL29:AL46=O77)*(AN29:AN46=F74)*(ISNUM BER(SEARCH(F74,AN29:AN46)))) -- __________________________________ HTH Bob "Monte" wrote in message ... 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 -- Daily Tracker |
Sumproduct, single criteria identified within multiple in sing
I didn't spot that. Why are you doing it twice?
-- __________________________________ HTH Bob "Monte" wrote in message ... =SUMPRODUCT((AL29:AL46=O77)*(ISNUMBER(SEARCH(F74,A N29: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 -- Daily Tracker "Bob Phillips" wrote: Try =SUMPRODUCT((AL29:AL46=O77)*(AN29:AN46=F74)*(ISNUM BER(SEARCH(F74,AN29:AN46)))) -- __________________________________ HTH Bob "Monte" wrote in message ... 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 -- Daily Tracker |
Sumproduct, single criteria identified within multiple in sing
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 -- Daily Tracker "Bob Phillips" wrote: I didn't spot that. Why are you doing it twice? -- __________________________________ HTH Bob "Monte" wrote in message ... =SUMPRODUCT((AL29:AL46=O77)*(ISNUMBER(SEARCH(F74,A N29: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 -- Daily Tracker "Bob Phillips" wrote: Try =SUMPRODUCT((AL29:AL46=O77)*(AN29:AN46=F74)*(ISNUM BER(SEARCH(F74,AN29:AN46)))) -- __________________________________ HTH Bob "Monte" wrote in message ... 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 -- Daily Tracker |
| All times are GMT +1. The time now is 05:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com