Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total Summed Count of Numeric Repeat (Paired /Double Instance) Criteria
Hi All,
Using the Named Range Sales, I would like a Formula to Sum Row Count by a specific Month for a specific Numeric Value "repeated" in consecutive Rows (paired/ double instance). The Summed Count required is for Numeric Value 51 in the Sample Data below. A numeric value will appear only once in a Row Input cell for criteria Numeric Value (will vary) Input cell for criteria Month (will vary) Data Layout Dynamic Named Range Sales - spans 8 Columns and many Rows: Column 1 - REF (reference) sequential ascending order Column 2 - DATE full date (16/03/2006) ascending order Column 3-8 - RESULTS (6 columns) numeric values ascending order Sample Data: Col 1 Col 2 Col 3 Col 4 Col 5 Col 6 Col 7 Col 8 REF DATE RESULTS 1 08/09/1998 51 54 59 60 61 70 2 17/10/1998 66 57 62 63 64 73 3 19/03/1998 51 60 65 66 67 76 4 20/03/1999 70 63 68 69 70 79 5 26/11/1999 51 66 71 72 73 82 6 20/12/1999 45 69 74 75 76 85 7 21/01/2000 51 72 77 78 79 88 8 11/02/2000 76 75 80 81 82 91 9 11/03/2000 51 78 83 84 85 94 10 16/03/2000 48 51 86 87 88 97 11 01/03/2001 60 65 89 51 91 100 12 23/03/2001 47 50 51 60 94 103 13 11/04/2001 45 51 54 64 97 106 14 19/06/2002 68 70 71 78 100 109 15 11/03/2003 65 70 71 72 103 112 16 16/04/2003 67 80 84 86 106 115 17 06/03/2004 40 43 47 50 51 118 18 17/03/2004 42 43 51 84 100 121 19 18/04/2004 41 42 51 55 76 80 Expected Result: The correct Summed Count for Numeric Value 51 Paired /Doulble Repeats Consecutively in a Row is 3. Each paired consecutive Row appearance is a count of 1 (one) References 9 and 10 = a count of 1 References 11 and 12 = a count of 1 References 17 and 18 = a count of 1 NB: Row 19 is excluded: although a consecutive appearance - it is a triple instance. I've tried to get the answer using SUMPRODUCT but unsuccessful. Help much appreciated. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200603/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total Summed Count of Numeric Repeat (Paired /Double Instance) Criteria
Hi All,
The specific Month is March. The specific Numeric Value is 51 A numeric value will appear only once in a Row Input cell for criteria Numeric Value (will vary) Input cell for criteria Month (will vary) Expected Result: The correct Summed Count for Numeric Value 51 Paired /Doulble Repeats Consecutively in a Row is 3. Each paired consecutive Row appearance is a count of 1 (one) References 9 and 10 = a count of 1 References 11 and 12 = a count of 1 References 17 and 18 = a count of 1 Thanks Sam Sam wrote: Hi All, Using the Named Range Sales, I would like a Formula to Sum Row Count by a specific Month for a specific Numeric Value "repeated" in consecutive Rows (paired/ double instance). The Summed Count required is for Numeric Value 51 in the Sample Data below. A numeric value will appear only once in a Row Input cell for criteria Numeric Value (will vary) Input cell for criteria Month (will vary) Data Layout Dynamic Named Range Sales - spans 8 Columns and many Rows: Column 1 - REF (reference) sequential ascending order Column 2 - DATE full date (16/03/2006) ascending order Column 3-8 - RESULTS (6 columns) numeric values ascending order Sample Data: Col 1 Col 2 Col 3 Col 4 Col 5 Col 6 Col 7 Col 8 REF DATE RESULTS 1 08/09/1998 51 54 59 60 61 70 2 17/10/1998 66 57 62 63 64 73 3 19/03/1998 51 60 65 66 67 76 4 20/03/1999 70 63 68 69 70 79 5 26/11/1999 51 66 71 72 73 82 6 20/12/1999 45 69 74 75 76 85 7 21/01/2000 51 72 77 78 79 88 8 11/02/2000 76 75 80 81 82 91 9 11/03/2000 51 78 83 84 85 94 10 16/03/2000 48 51 86 87 88 97 11 01/03/2001 60 65 89 51 91 100 12 23/03/2001 47 50 51 60 94 103 13 11/04/2001 45 51 54 64 97 106 14 19/06/2002 68 70 71 78 100 109 15 11/03/2003 65 70 71 72 103 112 16 16/04/2003 67 80 84 86 106 115 17 06/03/2004 40 43 47 50 51 118 18 17/03/2004 42 43 51 84 100 121 19 18/04/2004 41 42 51 55 76 80 Expected Result: The correct Summed Count for Numeric Value 51 Paired /Doulble Repeats Consecutively in a Row is 3. Each paired consecutive Row appearance is a count of 1 (one) References 9 and 10 = a count of 1 References 11 and 12 = a count of 1 References 17 and 18 = a count of 1 NB: Row 19 is excluded: although a consecutive appearance - it is a triple instance. I've tried to get the answer using SUMPRODUCT but unsuccessful. Help much appreciated. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200603/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total Summed Count of Numeric Repeat (Paired /Double Instance) Criteria
Assuming that A1:H19 contains your data, let J1 contain the month number
of interest, such as 3 for the month of March, and let K1 contain the numeric value of interest, such as 51, then try... I1: =IF(MONTH(B1)=J1,IF(ISNUMBER(MATCH(K1,C1:H1,0)),1, 0),0) I2, copied down: =IF(MONTH(B2)=$J$1,IF(ISNUMBER(MATCH($K$1,C2:H2,0) ),IF(I1<2,I1+1,1),0),0) K1: =COUNTIF(I1:I19,2) Hope this helps! In article <5d5d835193b61@uwe, "Sam via OfficeKB.com" <u4102@uwe wrote: Hi All, Using the Named Range Sales, I would like a Formula to Sum Row Count by a specific Month for a specific Numeric Value "repeated" in consecutive Rows (paired/ double instance). The Summed Count required is for Numeric Value 51 in the Sample Data below. A numeric value will appear only once in a Row Input cell for criteria Numeric Value (will vary) Input cell for criteria Month (will vary) Data Layout Dynamic Named Range Sales - spans 8 Columns and many Rows: Column 1 - REF (reference) sequential ascending order Column 2 - DATE full date (16/03/2006) ascending order Column 3-8 - RESULTS (6 columns) numeric values ascending order Sample Data: Col 1 Col 2 Col 3 Col 4 Col 5 Col 6 Col 7 Col 8 REF DATE RESULTS 1 08/09/1998 51 54 59 60 61 70 2 17/10/1998 66 57 62 63 64 73 3 19/03/1998 51 60 65 66 67 76 4 20/03/1999 70 63 68 69 70 79 5 26/11/1999 51 66 71 72 73 82 6 20/12/1999 45 69 74 75 76 85 7 21/01/2000 51 72 77 78 79 88 8 11/02/2000 76 75 80 81 82 91 9 11/03/2000 51 78 83 84 85 94 10 16/03/2000 48 51 86 87 88 97 11 01/03/2001 60 65 89 51 91 100 12 23/03/2001 47 50 51 60 94 103 13 11/04/2001 45 51 54 64 97 106 14 19/06/2002 68 70 71 78 100 109 15 11/03/2003 65 70 71 72 103 112 16 16/04/2003 67 80 84 86 106 115 17 06/03/2004 40 43 47 50 51 118 18 17/03/2004 42 43 51 84 100 121 19 18/04/2004 41 42 51 55 76 80 Expected Result: The correct Summed Count for Numeric Value 51 Paired /Doulble Repeats Consecutively in a Row is 3. Each paired consecutive Row appearance is a count of 1 (one) References 9 and 10 = a count of 1 References 11 and 12 = a count of 1 References 17 and 18 = a count of 1 NB: Row 19 is excluded: although a consecutive appearance - it is a triple instance. I've tried to get the answer using SUMPRODUCT but unsuccessful. Help much appreciated. Thanks Sam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table for reporting sales performance | Excel Discussion (Misc queries) | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |