software

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 quot;repeatedquot; 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 1Col 2Col 3Col 4Col 5Col 6Col 7Col 8
REFDATERESULTS
108/09/1998 515459606170
217/10/1998 665762636473
319/03/1998 516065666776
420/03/1999 706368697079
526/11/1999 516671727382
620/12/1999 456974757685
721/01/2000 517277787988
811/02/2000 767580818291
911/03/2000 517883848594
1016/03/2000 485186878897
1101/03/2001 6065895191100
1223/03/2001 4750516094103
1311/04/2001 4551546497106
1419/06/2002 68707178100109
1511/03/2003 65707172103112
1616/04/2003 67808486106115
1706/03/2004 4043475051118
1817/03/2004 42435184100121
1918/04/2004 414251557680Expected 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
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 1Thanks
Sam

Sam wrote:
gt;Hi All,
gt;
gt;Using the Named Range Sales, I would like a Formula to Sum Row Count by a
gt;specific Month for a specific Numeric Value quot;repeatedquot; in consecutive Rows
gt;(paired/ double instance). The Summed Count required is for Numeric Value 51
gt;in the Sample Data below.
gt;
gt;A numeric value will appear only once in a Row
gt;Input cell for criteria Numeric Value (will vary)
gt;Input cell for criteria Month (will vary)
gt;
gt;Data Layout
gt;Dynamic Named Range Sales - spans 8 Columns and many Rows:
gt;Column 1 - REF (reference) sequential ascending order
gt;Column 2 - DATE full date (16/03/2006) ascending order
gt;Column 3-8 - RESULTS (6 columns) numeric values ascending order
gt;
gt;Sample Data:
gt;
gt;Col 1Col 2Col 3Col 4Col 5Col 6Col 7Col 8
gt;REFDATERESULTS
gt;108/09/1998 515459606170
gt;217/10/1998 665762636473
gt;319/03/1998 516065666776
gt;420/03/1999 706368697079
gt;526/11/1999 516671727382
gt;620/12/1999 456974757685
gt;721/01/2000 517277787988
gt;811/02/2000 767580818291
gt;911/03/2000 517883848594
gt;1016/03/2000 485186878897
gt;1101/03/2001 6065895191100
gt;1223/03/2001 4750516094103
gt;1311/04/2001 4551546497106
gt;1419/06/2002 68707178100109
gt;1511/03/2003 65707172103112
gt;1616/04/2003 67808486106115
gt;1706/03/2004 4043475051118
gt;1817/03/2004 42435184100121
gt;1918/04/2004 414251557680
gt;
gt;Expected Result:
gt;The correct Summed Count for Numeric Value 51 Paired /Doulble Repeats
gt;Consecutively in a Row is 3.
gt;Each paired consecutive Row appearance is a count of 1 (one)
gt;References 9 and 10 = a count of 1
gt;References 11 and 12 = a count of 1
gt;References 17 and 18 = a count of 1
gt;
gt;NB: Row 19 is excluded: although a consecutive appearance - it is a triple
gt;instance.
gt;
gt;I've tried to get the answer using SUMPRODUCT but unsuccessful.
gt;
gt;Help much appreciated.
gt;
gt;Thanks
gt;Sam

--
Message posted via OfficeKB.com
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(I1lt;2,I1 1,1),0),0)

K1:

=COUNTIF(I1:I19,2)

Hope this helps!

In article lt;5d5d835193b61@uwegt;, quot;Sam via OfficeKB.comquot; lt;u4102@uwegt;
wrote:

gt; Hi All,
gt;
gt; Using the Named Range Sales, I would like a Formula to Sum Row Count by a
gt; specific Month for a specific Numeric Value quot;repeatedquot; in consecutive Rows
gt; (paired/ double instance). The Summed Count required is for Numeric Value 51
gt; in the Sample Data below.
gt;
gt; A numeric value will appear only once in a Row
gt; Input cell for criteria Numeric Value (will vary)
gt; Input cell for criteria Month (will vary)
gt;
gt; Data Layout
gt; Dynamic Named Range Sales - spans 8 Columns and many Rows:
gt; Column 1 - REF (reference) sequential ascending order
gt; Column 2 - DATE full date (16/03/2006) ascending order
gt; Column 3-8 - RESULTS (6 columns) numeric values ascending order
gt;
gt; Sample Data:
gt;
gt; Col 1Col 2Col 3Col 4Col 5Col 6Col 7Col 8
gt; REFDATERESULTS
gt; 108/09/1998 515459606170
gt; 217/10/1998 665762636473
gt; 319/03/1998 516065666776
gt; 420/03/1999 706368697079
gt; 526/11/1999 516671727382
gt; 620/12/1999 456974757685
gt; 721/01/2000 517277787988
gt; 811/02/2000 767580818291
gt; 911/03/2000 517883848594
gt; 1016/03/2000 485186878897
gt; 1101/03/2001 6065895191100
gt; 1223/03/2001 4750516094103
gt; 1311/04/2001 4551546497106
gt; 1419/06/2002 68707178100109
gt; 1511/03/2003 65707172103112
gt; 1616/04/2003 67808486106115
gt; 1706/03/2004 4043475051118
gt; 1817/03/2004 42435184100121
gt; 1918/04/2004 414251557680
gt;
gt;
gt; Expected Result:
gt; The correct Summed Count for Numeric Value 51 Paired /Doulble Repeats
gt; Consecutively in a Row is 3.
gt; Each paired consecutive Row appearance is a count of 1 (one)
gt; References 9 and 10 = a count of 1
gt; References 11 and 12 = a count of 1
gt; References 17 and 18 = a count of 1
gt;
gt; NB: Row 19 is excluded: although a consecutive appearance - it is a triple
gt; instance.
gt;
gt; I've tried to get the answer using SUMPRODUCT but unsuccessful.
gt;
gt; Help much appreciated.
gt;
gt; Thanks
gt; Sam