Sum Count of Two Numbers in the same Row

Hi All,

Dynamic Named Range "Results" spans 5 Columns and many Rows. Each cell
houses numeric single-digit or double-digit values.

I would like a Formula to find / match all instances of any two particular
numbers (single-digit / double-digit) in the same Row and Sum the Count of
their total occurrences together. For instance, how many times, if any, does
80 and 87 appear together in the same Row. I would like to refer to the
values using two cell references.

Thanks
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200509/1

shouldn't a simple =COUNTIF( do it for you?

--
Don Guillett
SalesAid Software
donaldb@281.com
"Sam via OfficeKB.com" <forum@OfficeKB.com> wrote in message
news:5483AE0BB7D48@OfficeKB.com...
> Hi All,
>
> Dynamic Named Range "Results" spans 5 Columns and many Rows. Each cell
> houses numeric single-digit or double-digit values.
>
> I would like a Formula to find / match all instances of any two particular
> numbers (single-digit / double-digit) in the same Row and Sum the Count of
> their total occurrences together. For instance, how many times, if any,
does
> 80 and 87 appear together in the same Row. I would like to refer to the
> values using two cell references.
>
> Thanks
> Sam
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200509/1

Assuming that A1 contains your first criterion, such as 80, and B1
contains your second criterion, such as 87, try...

=SUM(--(MMULT(--(Results=A1),TRANSPOSE(COLUMN(Results)^0))*MMULT(--(Resul
ts=B1),TRANSPOSE(COLUMN(Results)^0))>0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <5483AE0BB7D48@OfficeKB.com>,
"Sam via OfficeKB.com" <forum@OfficeKB.com> wrote:

> Hi All,
>
> Dynamic Named Range "Results" spans 5 Columns and many Rows. Each cell
> houses numeric single-digit or double-digit values.
>
> I would like a Formula to find / match all instances of any two particular
> numbers (single-digit / double-digit) in the same Row and Sum the Count of
> their total occurrences together. For instance, how many times, if any, does
> 80 and 87 appear together in the same Row. I would like to refer to the
> values using two cell references.
>
> Thanks
> Sam

Hi Don,

Don Guillett wrote:
>shouldn't a simple =COUNTIF( do it for you?

I don't get the expected results using a simple =COUNTIF. Could you,
possibly provide an example based on:

Dynamic Named Range "Results" spans 5 Columns and many Rows. Each cell
houses numeric single-digit or double-digit values.

I would like a Formula to find / match all instances of any two particular
numbers (single-digit / double-digit) in the same Row and Sum the Count of
their total occurrences together. For instance, how many times, if any, does
80 and 87 appear together in the same Row. I would like to refer to the
values using two cell references.

Much appreciated.
Cheers,
Sam

Don Guillett wrote:
>shouldn't a simple =COUNTIF( do it for you?
>
>> Hi All,
>>
>[quoted text clipped - 9 lines]
>> Thanks
>> Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200509/1

Do the two numbers always occur consecutively in the row?

Do the two numbers only appear together once in the row?

If so, try the following instead...

Assumptions:

D1:H10 contains your 5 columns of data

A1 contains your first criterion, such as 80

B1 contains your second criterion, such as 87

Formula:

=SUM(MMULT((D1:G10=A1)*(E1:H10=B1),TRANSPOSE(COLUMN(D1:G10)^0)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <5483AE0BB7D48@OfficeKB.com>,
"Sam via OfficeKB.com" <forum@OfficeKB.com> wrote:

> Hi All,
>
> Dynamic Named Range "Results" spans 5 Columns and many Rows. Each cell
> houses numeric single-digit or double-digit values.
>
> I would like a Formula to find / match all instances of any two particular
> numbers (single-digit / double-digit) in the same Row and Sum the Count of
> their total occurrences together. For instance, how many times, if any, does
> 80 and 87 appear together in the same Row. I would like to refer to the
> values using two cell references.
>
> Thanks
> Sam

Hi Domenic,

Thank you very much for assistance and a brilliant Formula. The Formula is
returning the expected results.

Is it possible to tweak the Formula based on my matrix type layout below.
The results are being returned to a chart / matrix layout: I have the
criterion vertically and horizontally and then they are referenced using the
horizontal and vertical cell address that houses the criterion, and the
result is returned to the intercept of the vertical and horizontal criterion.
So at some point both criterion values being referenced will be the same, if
possible can the Formula return a zero when this occurs?

Example:
Cell Ref. A2 and B1 criterion 80 and 80
Cell Ref. A3 and C1 criterion 81and 81


Criteria B1 houses 80, C1 houses 81, D1 houses 82, E1
houses 83, etc
A2 houses 80 0 4
9 7
A3 houses 81 5 0
3 2
A4 houses 82 7 3
0 1
A5 houses 83 2 7
5 0


So, I would use your Formula with the corresponding vertical and horizontal
cell references (and copy across and down):
=SUM(--(MMULT(--(Results=$A2),TRANSPOSE(COLUMN(Results)^0))*MMULT(--
(Results=B$1),TRANSPOSE(COLUMN(Results)^0))>0))
....confirmed with CONTROL+SHIFT+ENTER.

Cheers,
Sam

Domenic wrote:
>Assuming that A1 contains your first criterion, such as 80, and B1
>contains your second criterion, such as 87, try...
>
>=SUM(--(MMULT(--(Results=A1),TRANSPOSE(COLUMN(Results)^0))*MMULT(--(Resul
>ts=B1),TRANSPOSE(COLUMN(Results)^0))>0))
>
>...confirmed with CONTROL+SHIFT+ENTER.
>
>Hope this helps!
>
>> Hi All,
>>
>[quoted text clipped - 9 lines]
>> Thanks
>> Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200509/1

Hi Domenic,

Just replied to your earlier Post. Thanks for further input.

Domenic wrote:
>Do the two numbers always occur consecutively in the row?
No

>Do the two numbers only appear together once in the row?
Yes

>If so, try the following instead...
>
>Assumptions:
>
>D1:H10 contains your 5 columns of data
>
>A1 contains your first criterion, such as 80
>
>B1 contains your second criterion, such as 87
>
>Formula:
>
>=SUM(MMULT((D1:G10=A1)*(E1:H10=B1),TRANSPOSE(COLUMN(D1:G10)^0)))
>
>...confirmed with CONTROL+SHIFT+ENTER.
>
>Hope this helps!

Cheers
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200509/1

Try...

=IF($A2<>B$1,SUM(--(MMULT(--(Results=$A2),TRANSPOSE(COLUMN(Results)^0))*M
MULT(--(Results=B$1),TRANSPOSE(COLUMN(Results)^0))>0)),0)

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <54860E366C004@OfficeKB.com>,
"Sam via OfficeKB.com" <forum@OfficeKB.com> wrote:

> Hi Domenic,
>
> Thank you very much for assistance and a brilliant Formula. The Formula is
> returning the expected results.
>
> Is it possible to tweak the Formula based on my matrix type layout below.
> The results are being returned to a chart / matrix layout: I have the
> criterion vertically and horizontally and then they are referenced using the
> horizontal and vertical cell address that houses the criterion, and the
> result is returned to the intercept of the vertical and horizontal criterion.
> So at some point both criterion values being referenced will be the same, if
> possible can the Formula return a zero when this occurs?
>
> Example:
> Cell Ref. A2 and B1 criterion 80 and 80
> Cell Ref. A3 and C1 criterion 81and 81
>
>
> Criteria B1 houses 80, C1 houses 81, D1 houses 82, E1
> houses 83, etc
> A2 houses 80 0 4
> 9 7
> A3 houses 81 5 0
> 3 2
> A4 houses 82 7 3
> 0 1
> A5 houses 83 2 7
> 5 0
>
>
> So, I would use your Formula with the corresponding vertical and horizontal
> cell references (and copy across and down):
> =SUM(--(MMULT(--(Results=$A2),TRANSPOSE(COLUMN(Results)^0))*MMULT(--
> (Results=B$1),TRANSPOSE(COLUMN(Results)^0))>0))
> ...confirmed with CONTROL+SHIFT+ENTER.
>
> Cheers,
> Sam
>
> Domenic wrote:
> >Assuming that A1 contains your first criterion, such as 80, and B1
> >contains your second criterion, such as 87, try...
> >
> >=SUM(--(MMULT(--(Results=A1),TRANSPOSE(COLUMN(Results)^0))*MMULT(--(Resul
> >ts=B1),TRANSPOSE(COLUMN(Results)^0))>0))
> >
> >...confirmed with CONTROL+SHIFT+ENTER.
> >
> >Hope this helps!
> >
> >> Hi All,
> >>
> >[quoted text clipped - 9 lines]
> >> Thanks
> >> Sam

Hi Domenic,

Great! Thank you so much.

Domenic wrote:
>Try...
>
>=IF($A2<>B$1,SUM(--(MMULT(--(Results=$A2),TRANSPOSE(COLUMN(Results)^0))*M
>MULT(--(Results=B$1),TRANSPOSE(COLUMN(Results)^0))>0)),0)
>
>...confirmed with CONTROL+SHIFT+ENTER.

Cheers,
Sam

Domenic wrote:
>Try...
>
>=IF($A2<>B$1,SUM(--(MMULT(--(Results=$A2),TRANSPOSE(COLUMN(Results)^0))*M
>MULT(--(Results=B$1),TRANSPOSE(COLUMN(Results)^0))>0)),0)
>
>...confirmed with CONTROL+SHIFT+ENTER.
>
>Hope this helps!
>
>> Hi Domenic,
>>
>[quoted text clipped - 48 lines]
>> >> Thanks
>> >> Sam


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