Count and Sum Total occurrances of two specific numbers

Hi All,

I have a table of numbers that spans nine Columns (A-I)and 400+ Rows (20-
480). I have made

the table a Dynamic Range called "Numbers". Each Row contains numbers in
ascending order. Is

there a formula that can check for two specific numbers Row by Row through
the (nine column)

Range "Numbers" and give a final Count of how many times the two numbers
appear together in

each Row of the Dynamic Range "Numbers"?

Columns A-I
Rows 20-480

Example sample data from Range "Numbers"

ROW20 51 58 59 65 69 72 73 76 79
ROW31 50 51 58 72 73 76 79 80 81
ROW50 50 52 60 62 68 69 70 75 76
ROW75 53 54 59 60 62 69 70 72 73
ROW80 50 51 58 59 70 71 72 73 76
ROW83 51 53 65 67 68 69 78 80 81
ROW94 51 52 58 60 61 65 67 72 73
ROW99 50 53 57 62 63 68 70 71 73

Example Criteria:
Count how many times 72 AND 73 appear together in the same Row throughout
the Dynamic Range

"Numbers".

Expected Result from sample Range "Numbers":
COUNT=5
72 AND 73 appear together a total of 5 times - Rows 20, 31, 75, 80 and 94.

Regards,
Sam

--
Message posted via http://www.officekb.com

Sam,

If the 73 is always in the same row in the column after the 72, then

=SUMPRODUCT((Numbers=72)*(OFFSET(Numbers,0,1)=73))

If the numbers could appear randomly throughout the row, then another
solution whould be required.

HTH,
Bernie
MS Excel MVP


"Sam via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
> Hi All,
>
> I have a table of numbers that spans nine Columns (A-I)and 400+ Rows (20-
> 480). I have made
>
> the table a Dynamic Range called "Numbers". Each Row contains numbers in
> ascending order. Is
>
> there a formula that can check for two specific numbers Row by Row through
> the (nine column)
>
> Range "Numbers" and give a final Count of how many times the two numbers
> appear together in
>
> each Row of the Dynamic Range "Numbers"?
>
> Columns A-I
> Rows 20-480
>
> Example sample data from Range "Numbers"
>
> ROW20 51 58 59 65 69 72 73 76 79
> ROW31 50 51 58 72 73 76 79 80 81
> ROW50 50 52 60 62 68 69 70 75 76
> ROW75 53 54 59 60 62 69 70 72 73
> ROW80 50 51 58 59 70 71 72 73 76
> ROW83 51 53 65 67 68 69 78 80 81
> ROW94 51 52 58 60 61 65 67 72 73
> ROW99 50 53 57 62 63 68 70 71 73
>
> Example Criteria:
> Count how many times 72 AND 73 appear together in the same Row throughout
> the Dynamic Range
>
> "Numbers".
>
> Expected Result from sample Range "Numbers":
> COUNT=5
> 72 AND 73 appear together a total of 5 times - Rows 20, 31, 75, 80 and 94.
>
> Regards,
> Sam
>
> --
> Message posted via http://www.officekb.com

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:uVMOMi%[email protected]...
> Sam,
>
> If the 73 is always in the same row in the column after the 72, then
>
> =SUMPRODUCT((Numbers=72)*(OFFSET(Numbers,0,1)=73))
>
> If the numbers could appear randomly throughout the row, then another
> solution whould be required.

Here is such a solution

=SUMPRODUCT(--((COUNTIF(INDIRECT("B"&ROW(numbers)&":G"&ROW(numbers)),72)>0)*
(COUNTIF(INDIRECT("B"&ROW(numbers)&":G"&ROW(numbers)),73)>0)))

Correction, to align with your columns

=SUMPRODUCT(--((COUNTIF(INDIRECT("A"&ROW(numbers)&":I"&ROW(numbers)),72)>0)*
(COUNTIF(INDIRECT("A"&ROW(numbers)&":I"&ROW(numbers)),73)>0)))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" <[email protected]> wrote in message
news:ebxJyq%[email protected]...
>
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:uVMOMi%[email protected]...
> > Sam,
> >
> > If the 73 is always in the same row in the column after the 72, then
> >
> > =SUMPRODUCT((Numbers=72)*(OFFSET(Numbers,0,1)=73))
> >
> > If the numbers could appear randomly throughout the row, then another
> > solution whould be required.
>
> Here is such a solution
>
>
=SUMPRODUCT(--((COUNTIF(INDIRECT("B"&ROW(numbers)&":G"&ROW(numbers)),72)>0)*
> (COUNTIF(INDIRECT("B"&ROW(numbers)&":G"&ROW(numbers)),73)>0)))
>
>

Hi Bob,

Thank you for your time and assistance. I've tried your formula but get a
count of zero, I know there should definitely be a count of more than zero.

=SUMPRODUCT(--((COUNTIF(INDIRECT("A"&ROW(numbers)&":I"&ROW(numbers)),72)>0)
*(COUNTIF(INDIRECT("A"&ROW(numbers)&":I"&ROW(numbers)),73)>0)))

Further help appreciated.

Regards,
Sam

--
Message posted via http://www.officekb.com

Hi Bernie,

Thank you for your time and assistance. Your formula provides the required
result.

=SUMPRODUCT((Numbers=72)*(OFFSET(Numbers,0,1)=73))

Thanks again,
Sam

--
Message posted via http://www.officekb.com

Hi Bob,

Please ignore my previous reply to you. All is clear now.

Missed your link to Bernie's Post -

"If the numbers could appear randomly throughout the row, then another
solution whould be required".

Here is such a solution
=SUMPRODUCT(--((COUNTIF(INDIRECT("B"&ROW(numbers)&":G"&ROW(numbers)),72)>0)
*(COUNTIF(INDIRECT("B"&ROW(numbers)&":G"&ROW(numbers)),73)>0)))

Thanks
Sam

--
Message posted via http://www.officekb.com

Sam,

My formula works whether they are adjacent or not, so you should not get a
zero, and also works if the 73 is before the 72, whereas Bernie's expects
72,73 strictly in order, strictly adjacent..

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sam via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob,
>
> Please ignore my previous reply to you. All is clear now.
>
> Missed your link to Bernie's Post -
>
> "If the numbers could appear randomly throughout the row, then another
> solution whould be required".
>
> Here is such a solution
>
=SUMPRODUCT(--((COUNTIF(INDIRECT("B"&ROW(numbers)&":G"&ROW(numbers)),72)>0)
> *(COUNTIF(INDIRECT("B"&ROW(numbers)&":G"&ROW(numbers)),73)>0)))
>
> Thanks
> Sam
>
> --
> Message posted via http://www.officekb.com

Hi Bob,

> Correction, to align with your columns
> =SUMPRODUCT(--((COUNTIF(INDIRECT("A"&ROW(numbers)&":I"&ROW(numbers)),72)>0)*
> (COUNTIF(INDIRECT("A"&ROW(numbers)&":I"&ROW(numbers)),73)>0)))

Also these array formulas (not dependent on any location) :

=SUM(--(0<MMULT(--(Numbers=72),TRANSPOSE(COLUMN(Numbers)*0+1))
*MMULT(--(Numbers=73),TRANSPOSE(COLUMN(Numbers)*0+1))))

or

=COUNT(IF(MMULT(--(Numbers=72),TRANSPOSE(COLUMN(Numbers)^0)),
1/MMULT(--(Numbers=73),TRANSPOSE(COLUMN(Numbers)^0))))

Regards,

Daniel M.

Hi Daniel,

"Daniel.M" <[email protected]> wrote in message
news:[email protected]...
>
> Also these array formulas (not dependent on any location) :
>
> =SUM(--(0<MMULT(--(Numbers=72),TRANSPOSE(COLUMN(Numbers)*0+1))
> *MMULT(--(Numbers=73),TRANSPOSE(COLUMN(Numbers)*0+1))))

MMULT is not an oft used function by me, a bit like SIGN was until Frank
pointed out some uses to me. Must keep it in mind.

> or
>
> =COUNT(IF(MMULT(--(Numbers=72),TRANSPOSE(COLUMN(Numbers)^0)),
> 1/MMULT(--(Numbers=73),TRANSPOSE(COLUMN(Numbers)^0))))

It's always nice to see a solution by a real formula man :-)

Regards

Bob

Hi Bob,

I've used your second version of the formula where the column letters
matched mine. I cannot see why I get zero.

Regards,
Sam

--
Message posted via http://www.officekb.com