Sum Count of Two Numbers in the same Row

[Sam via 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

 

[Don Guillett]

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

 

[Domenic]

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!

 

[Sam via OfficeKB.com]

Hi Don,

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

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

Hi All,

[quoted text clipped - 9 lines]

Thanks
Sam

 

[Domenic]

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!

 

[Sam via OfficeKB.com]

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

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

 

[Sam via OfficeKB.com]

Hi Domenic,

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

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

 

[Domenic]

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,

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

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

 

[Sam via OfficeKB.com]

Hi Domenic,

Great! Thank you so much.

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

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]