Total Summed Count of Numeric Repeat (Paired /Double Instance) Criteria

[Sam via OfficeKB.com]

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

 

[Sam via OfficeKB.com]

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

 

[Domenic]

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!