| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
trying to COUNT occurrences when certain criteria is met
Bob,
Thanks for your reply but it did not assist. I received the #value response. Perhaps someone can assist with this. See the initial question and Bob's reply after my brief example. Thanks Allan I will give you a brief example of what I am after. Table A1 = 320 A2 = 767 A3 = 73H (NON NUMERIC VALUE) A4 = 744 A5 = 737 The data sheet E1 = 320 L1 = 100 E2 = 737 L2 = 99 E3 = 320 L3 = 121 E4 = 747 L4 = 35 E5 = 320 L5 = 190 E6 = 767 L6 = 130 E7 = 737 L7 = 145 The result that I would expect is 320 = 2 (only 2 occurrecnces 120) 767 = 1 (only 1 occurrence 120) 73H = 0 ( no matching data) 744 = 0 ( no OCCURRENCE 120) 737 = 1 ( only 1 occurrence 120) I hope that this small example explains my problem. By the way, what does -- indicate or mean? Thanks again. Allan "Bob Phillips" wrote: =SUMPRODUCT(--(ISNUMBER(M ATCH(Table!A1:A15,E1:A10000,0))),--(L1:L10000120)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Allan from Melbourne" wrote in message ... Hello Hopefully some kind person can assist with this. I have a worksheet with n number of rows (can vary from 30 to 3000) and columns from a to m (13). I need to count the number of times that column L value exceeds 120 when column E = a pre defined value. This pre defined value can equal 320, 744, 743, 73H and many more. I have a table set up that has these required "pre defined values". Keep in mind that this table is dynamic, values can be added or deleted. The reference for this table is on another worksheet "table" A1..A15. I don't mind where this table is located, it just happens to be in this seperate worksheet. I can sort or filter however I was wondering if there was a better way to count the occurrences. Many thanks Regards Allan |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
trying to COUNT occurrences when certain criteria is met
Try this and copy down for Table!A2 etc:
=SUMPRODUCT(--($E$1:$E$10000=Table!A1),--($L$1:$L$10000120)) -- converts True False to 1/0 so calculations/counts can be done. HTH "Allan from Melbourne" wrote: Bob, Thanks for your reply but it did not assist. I received the #value response. Perhaps someone can assist with this. See the initial question and Bob's reply after my brief example. Thanks Allan I will give you a brief example of what I am after. Table A1 = 320 A2 = 767 A3 = 73H (NON NUMERIC VALUE) A4 = 744 A5 = 737 The data sheet E1 = 320 L1 = 100 E2 = 737 L2 = 99 E3 = 320 L3 = 121 E4 = 747 L4 = 35 E5 = 320 L5 = 190 E6 = 767 L6 = 130 E7 = 737 L7 = 145 The result that I would expect is 320 = 2 (only 2 occurrecnces 120) 767 = 1 (only 1 occurrence 120) 73H = 0 ( no matching data) 744 = 0 ( no OCCURRENCE 120) 737 = 1 ( only 1 occurrence 120) I hope that this small example explains my problem. By the way, what does -- indicate or mean? Thanks again. Allan "Bob Phillips" wrote: =SUMPRODUCT(--(ISNUMBER(M ATCH(Table!A1:A15,E1:A10000,0))),--(L1:L10000120)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Allan from Melbourne" wrote in message ... Hello Hopefully some kind person can assist with this. I have a worksheet with n number of rows (can vary from 30 to 3000) and columns from a to m (13). I need to count the number of times that column L value exceeds 120 when column E = a pre defined value. This pre defined value can equal 320, 744, 743, 73H and many more. I have a table set up that has these required "pre defined values". Keep in mind that this table is dynamic, values can be added or deleted. The reference for this table is on another worksheet "table" A1..A15. I don't mind where this table is located, it just happens to be in this seperate worksheet. I can sort or filter however I was wondering if there was a better way to count the occurrences. Many thanks Regards Allan |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
trying to COUNT occurrences when certain criteria is met
Sorry, I read that as matching all values not singletons. Try
=SUMPRODUCT(--(E1:E10000=320),--(L1:L10000120)) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Allan from Melbourne" wrote in message ... Bob, Thanks for your reply but it did not assist. I received the #value response. Perhaps someone can assist with this. See the initial question and Bob's reply after my brief example. Thanks Allan I will give you a brief example of what I am after. Table A1 = 320 A2 = 767 A3 = 73H (NON NUMERIC VALUE) A4 = 744 A5 = 737 The data sheet E1 = 320 L1 = 100 E2 = 737 L2 = 99 E3 = 320 L3 = 121 E4 = 747 L4 = 35 E5 = 320 L5 = 190 E6 = 767 L6 = 130 E7 = 737 L7 = 145 The result that I would expect is 320 = 2 (only 2 occurrecnces 120) 767 = 1 (only 1 occurrence 120) 73H = 0 ( no matching data) 744 = 0 ( no OCCURRENCE 120) 737 = 1 ( only 1 occurrence 120) I hope that this small example explains my problem. By the way, what does -- indicate or mean? Thanks again. Allan "Bob Phillips" wrote: =SUMPRODUCT(--(ISNUMBER(M ATCH(Table!A1:A15,E1:A10000,0))),--(L1:L10000120)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Allan from Melbourne" wrote in message ... Hello Hopefully some kind person can assist with this. I have a worksheet with n number of rows (can vary from 30 to 3000) and columns from a to m (13). I need to count the number of times that column L value exceeds 120 when column E = a pre defined value. This pre defined value can equal 320, 744, 743, 73H and many more. I have a table set up that has these required "pre defined values". Keep in mind that this table is dynamic, values can be added or deleted. The reference for this table is on another worksheet "table" A1..A15. I don't mind where this table is located, it just happens to be in this seperate worksheet. I can sort or filter however I was wondering if there was a better way to count the occurrences. Many thanks Regards Allan |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
trying to COUNT occurrences when certain criteria is met
Allan,
Try =SUMPRODUCT((A1 = $E$1:$E$3000)*($L$1:$L$3000120)) Copy down to match the number of entries you have in cells A1:A15 HTH, Bernie MS Excel MVP "Allan from Melbourne" wrote in message ... Bob, Thanks for your reply but it did not assist. I received the #value response. Perhaps someone can assist with this. See the initial question and Bob's reply after my brief example. Thanks Allan I will give you a brief example of what I am after. Table A1 = 320 A2 = 767 A3 = 73H (NON NUMERIC VALUE) A4 = 744 A5 = 737 The data sheet E1 = 320 L1 = 100 E2 = 737 L2 = 99 E3 = 320 L3 = 121 E4 = 747 L4 = 35 E5 = 320 L5 = 190 E6 = 767 L6 = 130 E7 = 737 L7 = 145 The result that I would expect is 320 = 2 (only 2 occurrecnces 120) 767 = 1 (only 1 occurrence 120) 73H = 0 ( no matching data) 744 = 0 ( no OCCURRENCE 120) 737 = 1 ( only 1 occurrence 120) I hope that this small example explains my problem. By the way, what does -- indicate or mean? Thanks again. Allan "Bob Phillips" wrote: =SUMPRODUCT(--(ISNUMBER(M ATCH(Table!A1:A15,E1:A10000,0))),--(L1:L10000120)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Allan from Melbourne" wrote in message ... Hello Hopefully some kind person can assist with this. I have a worksheet with n number of rows (can vary from 30 to 3000) and columns from a to m (13). I need to count the number of times that column L value exceeds 120 when column E = a pre defined value. This pre defined value can equal 320, 744, 743, 73H and many more. I have a table set up that has these required "pre defined values". Keep in mind that this table is dynamic, values can be added or deleted. The reference for this table is on another worksheet "table" A1..A15. I don't mind where this table is located, it just happens to be in this seperate worksheet. I can sort or filter however I was wondering if there was a better way to count the occurrences. Many thanks Regards Allan |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
trying to COUNT occurrences when certain criteria is met
Thanks to Toppers, Bob and Bernie. Your solutions worked and saved me plenty
of time and made the spreadsheet work a lot quicker. Thanks again Allan "Allan from Melbourne" wrote: Bob, Thanks for your reply but it did not assist. I received the #value response. Perhaps someone can assist with this. See the initial question and Bob's reply after my brief example. Thanks Allan I will give you a brief example of what I am after. Table A1 = 320 A2 = 767 A3 = 73H (NON NUMERIC VALUE) A4 = 744 A5 = 737 The data sheet E1 = 320 L1 = 100 E2 = 737 L2 = 99 E3 = 320 L3 = 121 E4 = 747 L4 = 35 E5 = 320 L5 = 190 E6 = 767 L6 = 130 E7 = 737 L7 = 145 The result that I would expect is 320 = 2 (only 2 occurrecnces 120) 767 = 1 (only 1 occurrence 120) 73H = 0 ( no matching data) 744 = 0 ( no OCCURRENCE 120) 737 = 1 ( only 1 occurrence 120) I hope that this small example explains my problem. By the way, what does -- indicate or mean? Thanks again. Allan "Bob Phillips" wrote: =SUMPRODUCT(--(ISNUMBER(M ATCH(Table!A1:A15,E1:A10000,0))),--(L1:L10000120)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Allan from Melbourne" wrote in message ... Hello Hopefully some kind person can assist with this. I have a worksheet with n number of rows (can vary from 30 to 3000) and columns from a to m (13). I need to count the number of times that column L value exceeds 120 when column E = a pre defined value. This pre defined value can equal 320, 744, 743, 73H and many more. I have a table set up that has these required "pre defined values". Keep in mind that this table is dynamic, values can be added or deleted. The reference for this table is on another worksheet "table" A1..A15. I don't mind where this table is located, it just happens to be in this seperate worksheet. I can sort or filter however I was wondering if there was a better way to count the occurrences. Many thanks Regards Allan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count non blank cells with criteria | Excel Discussion (Misc queries) | |||
| Count items when specific text and date criteria are met | Excel Worksheet Functions | |||
| Count rows that match 3 sets of criteria? | Excel Worksheet Functions | |||
| Count rows that match criteria in 2 different column cell ranges | New Users to Excel | |||
| count cells using multiple criteria | Excel Discussion (Misc queries) | |||
Hybrid Mode
