| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting Multiple columns using single criteria | Excel Worksheet Functions | |||
| Sum single or duplicate rows with multiple criteria | Excel Worksheet Functions | |||
| sumproduct with multiple criteria in single column | Excel Discussion (Misc queries) | |||
| Printing single pages from multiple worksheets in a single print job | Excel Discussion (Misc queries) | |||
| Find Multiple instances of Single Criterion in Row & Return To a Single Col | Excel Worksheet Functions | |||
Linear Mode
