SUMPRODUCT function

[Mike]

Good Morning All,
Using Windows XP & Excel XP


I have a worksheet that displays a daily 3 digit lottery number with the 3
digits being separted in column A2, B2 & C2
In columns A1,B1 & C1 is the number picked by a player.

Example:
A B C D
----------------------------
1 1 2 3 (Player pick)
2 4 1 7 (Lottery number drawn)
3 3 3 4 (Lottery number drawn)

I would like to have a formula that would count the number of occurences
that a player number matches a lottery drawn number, but with NO repeats.
Example:
In D2 I would like the formula to give the result of 1 (because of the match
of B2 matching A1).
In D3 I would like the formula to give the result of 1 (because A3 matches
up with C1 but NOT counting B3 matching up with C1

I was using the formula =SUMPRODUCT(COUNTIF($A2:$C2,$A$1:$C$1))...in D2,
that would give the result of 1.
but the same formula =SUMPRODUCT(COUNTIF($A3:$C3,$A$1:$C$1))...in D3 gives
me the result of 2....but I want it to count just a single occurence of the
3.
Is there a formula that would just count a single occurence of a number that
matches A1:C1?
Thank you in advance,
Michael

 

[Bob Phillips]

How about

=MIN(SUMPRODUCT(COUNTIF($A2:$C2,$A$1:$C$1)),1)

--

HTH

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

 

[Mike]

Hi Bob,
Thanks for the quick reply.
If I have in A2 "2", B2 "1" & C2 "5" it only gives me the count of 1, when
it should give me the value of 2, the 2 in A2 and the 1 in B2 match in
A1:C1

A B C D
---------------------------------------
1 1 2 3 (Players pick)
2 2 1 5 (Lottery number
picked)

=MIN(SUMPRODUCT(COUNTIF($A2:$C2,$A$1:$C$1)),1) in D2 gives me the count of
1, when it should be 2.

Thanks


----------------------------------

 

[Bob Phillips]

Hi Mike,

After I posted I thought you might want that. Here is an alternative

=SUMPRODUCT(--NOT(ISERROR(1/COUNTIF(A2:C2,$A$1:$C$1))))

--

HTH

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

 

[Mike]

It works greats, thanks so much Bob

 

[xirx]

Hi Mike,

After I posted I thought you might want that. Here is an alternative

=SUMPRODUCT(--NOT(ISERROR(1/COUNTIF(A2:C2,$A$1:$C$1))))

I'd very much like to _understand_ that.

Could someone explain or provide a pointer to an explanation of:

* the "--" before the NOT
* the 1/ before COUNTIF

thx

 

[Bob Phillips]

I'll have a shot, but it is quite complex, so my explanation may not be up
to it.

Firstly, each item in the row being tested is compared against row 1
COUNTIF(A2:C2,$A$1:$C$1)
This will return an array of values between 0 and the number of columns (3
in this case)

Then, each item of this array is divided into 1. The reason for this is to
force an error when the count from step 1 is 0.

These values are then forced into True/False values with the ISERROR
function, so that the 0 values (which change to #DIV/0 when divided into 1)
transform to True, the other values transform to False.

The NOT just flips True to False and vice versa.

Then the -- kicks in, which coerces the True/False values to 1 and 0.
SUMPRODUCT adds these up.

So as an example, if A1:C1 holds the values 1,2,3 and A2:C2 holds 3,3,2, we
get:
- COUNTIF returns an array of 0,1,2 (1 isn't found, 2 is found once, 3 is
found twice)
- 1/COUNTIF change the array to #DIV/0,1,0.5
- ISERROR(1/COUNTIF becomes True,False,False
- NOT(ISERROR(1/COUNTIF becomes False,True,True
- --NOT(ISERROR(1/COUNTIF becomes 0,1,1
- SUMPRODUCT adds them up to 2

If you want further explanation, the best explanation can be found at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--

HTH

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