Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count a Match set of values between cells
I want to produce a page within my workbook for results from my work page.
On my work page and have two columns F and G which down the page will have an array of entries in each. I want to count the number of occurances where something in column F and something in column G are equal to two different values. This is my example. F G TYPE GLAZING 5 S SG-3 6 C 7 S SG-3 8 C 9 S SG-3 For my results I want to know how many times that "S" and "SG-3" occur side by side in the same row as shown above. Can someone please enlighten me how to accomplish this task? Thank you, John M. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count a Match set of values between cells
On Wed, 17 Dec 2008 14:00:05 -0800, John M. <John
wrote: I want to produce a page within my workbook for results from my work page. On my work page and have two columns F and G which down the page will have an array of entries in each. I want to count the number of occurances where something in column F and something in column G are equal to two different values. This is my example. F G TYPE GLAZING 5 S SG-3 6 C 7 S SG-3 8 C 9 S SG-3 For my results I want to know how many times that "S" and "SG-3" occur side by side in the same row as shown above. Can someone please enlighten me how to accomplish this task? Thank you, John M. Try this formula: =SUMPRODUCT((F5:F9="S")*(G5:G9="SG-3")) Change the 5 and 9 on both places to cover the rows of data that you have. Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count a Match set of values between cells
Try this:
=SUMPRODUCT(--(F2:F100="S"),--(G2:G100="SG-3")) If you want the "S" and "SG-3" to referenced by a particular cell: =SUMPRODUCT(--(F2:F100=H1),--(G2:G100=H2)) Regards, Paul -- "John M." <John wrote in message ... I want to produce a page within my workbook for results from my work page. On my work page and have two columns F and G which down the page will have an array of entries in each. I want to count the number of occurances where something in column F and something in column G are equal to two different values. This is my example. F G TYPE GLAZING 5 S SG-3 6 C 7 S SG-3 8 C 9 S SG-3 For my results I want to know how many times that "S" and "SG-3" occur side by side in the same row as shown above. Can someone please enlighten me how to accomplish this task? Thank you, John M. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count a Match set of values between cells
Thanks guys this function did the trick but it is missing one thing and that
is the ability to overlook blank cells and return a false result. How would I properly add that into the formula? "PCLIVE" wrote: Try this: =SUMPRODUCT(--(F2:F100="S"),--(G2:G100="SG-3")) If you want the "S" and "SG-3" to referenced by a particular cell: =SUMPRODUCT(--(F2:F100=H1),--(G2:G100=H2)) Regards, Paul -- "John M." <John wrote in message ... I want to produce a page within my workbook for results from my work page. On my work page and have two columns F and G which down the page will have an array of entries in each. I want to count the number of occurances where something in column F and something in column G are equal to two different values. This is my example. F G TYPE GLAZING 5 S SG-3 6 C 7 S SG-3 8 C 9 S SG-3 For my results I want to know how many times that "S" and "SG-3" occur side by side in the same row as shown above. Can someone please enlighten me how to accomplish this task? Thank you, John M. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count a Match set of values between cells
If the cells are blank, then it should not be counted as a match. If there
are no matches, then the result should be zero. Do you mean that if there are no matches to both criteria, then you want the result to be false? If so then: =IF(SUMPRODUCT(--(F2:F100="S"),--(G2:G100="SG-3"))=0,"False",SUMPRODUCT(--(F2:F100="S"),--(G2:G100="SG-3")) If you meant something else, please elaborate. Regards, Paul -- "John M." wrote in message ... Thanks guys this function did the trick but it is missing one thing and that is the ability to overlook blank cells and return a false result. How would I properly add that into the formula? "PCLIVE" wrote: Try this: =SUMPRODUCT(--(F2:F100="S"),--(G2:G100="SG-3")) If you want the "S" and "SG-3" to referenced by a particular cell: =SUMPRODUCT(--(F2:F100=H1),--(G2:G100=H2)) Regards, Paul -- "John M." <John wrote in message ... I want to produce a page within my workbook for results from my work page. On my work page and have two columns F and G which down the page will have an array of entries in each. I want to count the number of occurances where something in column F and something in column G are equal to two different values. This is my example. F G TYPE GLAZING 5 S SG-3 6 C 7 S SG-3 8 C 9 S SG-3 For my results I want to know how many times that "S" and "SG-3" occur side by side in the same row as shown above. Can someone please enlighten me how to accomplish this task? Thank you, John M. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i count cells with values in a certain range ? | Excel Discussion (Misc queries) | |||
How to Search, Count, Match and Post Values | Excel Worksheet Functions | |||
Count cells with values x and <y | Excel Discussion (Misc queries) | |||
How do I count cells with non zero values? | Excel Discussion (Misc queries) | |||
Count cells with specific values in the cells next to them? | Excel Worksheet Functions |