Find and Count Frequency of Numeric Value in Non-Contiguous Rows

[Sam via OfficeKB.com]

Hi All,

My Numeric Data spans 61 columns and many rows. The Data comprises Numeric
Values and Numeric Labels. The Numeric Labels and Numeric Values are in 2
separate consecutive rows of data, a blank row and then a new set of Numeric
Labels and Values in 2 separate consecutive rows, a blank row etc.

I would like the total count of a specific Numeric Value (will vary). My Data
spans row F117:BO227. The count should start from row F118. Row F118:BO118
holds the first row of Numeric Values. Thereafter, every third row will hold
the remaining Numeric Values to be searched. Last Row of Numeric Values is
F226:B0226.

The Numeric Labels are 1-61
The Numeric Values are 0-1000.

Sample Layout:
Row F117:BO117 Numeric Labels (1-61)
Row F118:BO118 Numeric Values(0-200) in descending order
Row F119:BO119 Blank/ Empty Row
Row F120:BO120 Numeric Labels (1-61)
Row F121:BO121 Numeric Values(0-200) in descending order
Row F122:BO122 Blank/ Empty Row
Row F123:BO123 Numeric Labels (1-61)
Row F124:BO124 Numeric Values(0-200) in descending order
Row F125:BO125 Blank/ Empty Row
Row F126:BO126 Numeric Labels (1-61)
Row F127:BO127 Numeric Values(0-200) in descending order
Row F128:BO128 Blank/ Empty Row
Row F129:BO129 Numeric Labels (1-61)
Row F130:BO130 Numeric Values(0-200) in descending order
Row F131:BO131 Blank/ Empty Row
Row etc
Row etc
Row F225:BO225 Numeric Labels (1-61)
Row F226:BO226 Numeric Values(0-200) in descending order
Row F227:BO227 Blank/ Empty Row

To find the Total Times Numeric Value 60 appears - search Rows with Numeric
Values; F118:BO118, F121:BO121, F124:BO124, F127:BO127, F130:BO130, etc; etc;
last Row F226:BO226.
Each time 60 appears sum (accumulate) the count.

Thanks
Sam

 

[Roger Govier]

Hi Sam

Try
=SUMPRODUCT((F118:BO226=60)*(MOD(ROW(F118:BO226)+2,3)=0))

 

[Roger Govier]

Hi Sam

Try
=SUMPRODUCT((F118:BO226=60)*(MOD(ROW(F118:BO226)+2,3)=0))

 

[Sam via OfficeKB.com]

Hi Roger,

Thank you very much for your reply and assistance. Your Formula did the job.
Great!

=SUMPRODUCT((F118:BO226=60)*(MOD(ROW(F118:BO226)+2,3)=0))

Cheers,
Sam

Hi Sam

Try
=SUMPRODUCT((F118:BO226=60)*(MOD(ROW(F118:BO226)+2,3)=0))

Hi All,

[quoted text clipped - 50 lines]

Thanks
Sam

 

[Sam via OfficeKB.com]

Hi Roger,

Thank you very much for your reply and assistance. Your Formula did the job.
Great!

=SUMPRODUCT((F118:BO226=60)*(MOD(ROW(F118:BO226)+2,3)=0))

Cheers,
Sam

Hi Sam

Try
=SUMPRODUCT((F118:BO226=60)*(MOD(ROW(F118:BO226)+2,3)=0))

Hi All,

[quoted text clipped - 50 lines]

Thanks
Sam