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(COLUM N(D1:G10)^0)))
....confirmed with CONTROL+SHIFT+ENTER.
Hope this helps!
In article ,
"Sam via 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
|