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

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

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