SUMPRODUCT function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 21 Feb 2005 15:03 GMT

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

Reply to this Message

Bob Phillips - 21 Feb 2005 15:33 GMT

How about

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

Signature

HTH

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

> Good Morning All,
> Using Windows XP & Excel XP
[quoted text clipped - 27 lines]
> Thank you in advance,
> Michael

Reply to this Message

Mike - 21 Feb 2005 16:08 GMT

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

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

> How about
>
[quoted text clipped - 37 lines]
>> Thank you in advance,
>> Michael

Reply to this Message

Bob Phillips - 21 Feb 2005 16:21 GMT

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))))

Signature

HTH

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

> Hi Bob,
> Thanks for the quick reply.
[quoted text clipped - 55 lines]
> >> Thank you in advance,
> >> Michael

Reply to this Message

Mike - 21 Feb 2005 16:45 GMT

It works greats, thanks so much Bob

Reply to this Message

xirx - 28 Feb 2005 14:53 GMT

> 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

Reply to this Message

Bob Phillips - 28 Feb 2005 16:51 GMT

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

Signature

HTH

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

> > Hi Mike,
> >
[quoted text clipped - 10 lines]
>
> thx

Reply to this Message