#### SUMPRODUCT function

```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?
Michael

``` 0 2/21/2005 3:03:28 PM excel  39879 articles. 2 followers. 6 Replies 572 Views Similar Articles

[PageSpeed] 31

```How about

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

--

HTH

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

"Mike" <windme@cox.net> wrote in message
> 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?
> Michael
>
>

``` 0 2/21/2005 3:33:38 PM
```Hi Bob,
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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:u8cb3qCGFHA.3732@tk2msftngp13.phx.gbl...
>
> =MIN(SUMPRODUCT(COUNTIF(\$A2:\$C2,\$A\$1:\$C\$1)),1)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Mike" <windme@cox.net> wrote in message
>> 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?
>> Michael
>>
>>
>
>

``` 0 2/21/2005 4:08:54 PM
```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" <windme@cox.net> wrote in message
> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:u8cb3qCGFHA.3732@tk2msftngp13.phx.gbl...
> >
> > =MIN(SUMPRODUCT(COUNTIF(\$A2:\$C2,\$A\$1:\$C\$1)),1)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Mike" <windme@cox.net> wrote in message
> >> 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
> >>
> >>
> >
> >
>
>

``` 0 2/21/2005 4:21:33 PM
```It works greats, thanks so much Bob

``` 0 2/21/2005 4:45:24 PM
```Bob Phillips wrote:
> 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
``` 0 2/28/2005 2:53:59 PM
```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.

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)

"xirx" <xirx@gmx.de> wrote in message
> Bob Phillips wrote:
> > 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

``` 0 2/28/2005 4:51:07 PM Similar Artilces:

Sumproduct
Hi all, I should make a condition(AND) in my sumproduct formula with date format(yyyy/mm/dd), why it dosent accept my condition in one column, like this: =SUMPRODUCT((\$B\$30:\$B\$3000=\$A\$1)*(\$G\$30:\$G\$3000>=\$B\$1)*(\$G\$30:\$G\$3000<\$B\$2)*(\$J\$30:\$J\$3000)) even I dublicate date column(G) and change the formula to : =SUMPRODUCT((\$B\$30:\$B\$3000=\$A\$1)*(\$G\$30:\$G\$3000>=\$B\$1)*(\$H\$30:\$H\$3000<\$B\$2)*(\$J\$30:\$J\$3000)) but it dosen't work again, any idea to solve this problem would be appreciated. Bijan Hi Bijan I don't see anyting wrong with your formula. Instead of multipl...

functions
hi, is there a function that does this if a1=1, if true a2=b1, if false a2 stays as it was (EITHER WITH A ZERO VALUE OR THE VALUE THAT MAY HAVE BEEN ENTER PREVIOUSLY FOR EXAMPLE B2= 10 A1= 0 B1=0 B1 WILL BE INCREMENTED BY A MACRO A1=10 B1=1 A1=10 B1=2 THOUGHT AN IF FUNCTION WITHOUT THE FALSE BIT WOULD WORK BUT IT DIDNT ALL IT GAVE ME WAS THE TEXT "FALSE" Hi Carlos1973! I'm afraid that you can't use functions to do other than return a value to a cell. Closest you'll get is: A2: =IF(A1=1,B1,0) or: A2: =IF(A1=1,B1,C1) You ...

Sumproduct or??
If I have two columns of numbers: 1 50 2 40 1 20 4 10 3 30 1 50 How do I write a formula that will sum every number in column B that corresponds to a 1 in column A. The answer should be 120. Thanks for any help. I believe you will want to use a sumif() function here if your data starts in A1 then =sumif(A1:A6,1,B1:B6) On Dec 8, 10:19 am, Terry <Terr...@aol.com> wrote: > If I have two columns of numbers: > > 1 50 > 2 40 > 1 20 > 4 10 > 3 30 > 1 50 > > How do I write a formula that will sum every number in column B that > correspon...

Function that meets table requirement then multiplies by table fac
I'm looking for a function that will reference a cell then lookup that cell in a table and use the multiplier in the table. I'm not explaining very well so Im hoping that my example will help you understand: Column A b c Sector P&L w/o Admin P&L w/ Admin PC 517.9 PC 0.0 PC 0.0 TD 1,993.6 TD (2,508.0) TS 431.3 TS 355.7 Table reference: FD 1.20 PC 1.03 PR 1.52 SIJ 1.00 TD 1.32 TS 1.63 In column C I would like to put a formula that; if column A mactches column A in table reference th...

If SUMPRODUCT & Blank cells
I am running Excel 2003 and I am trying to count a range of data for charting. I am using the following formula: '=IF(SUMPRODUCT(('Assess'!\$H\$3:\$H\$189="Temp")*('Assess'!\$J\$3:\$J\$189<\$B \$15)*('Assess'!\$M\$3:\$M\$189="N")*('Assess'!\$A\$3:\$A\$189=A16) *1),SUMPRODUCT(('Assess'!\$H\$3:\$H\$189="Temp")*('Assess'!\$J\$3:\$J\$189<\$B \$15)*('Assess'!\$M\$3:\$M\$189="N")*('Assess'!\$A\$3:\$A\$189=A16)*1),0) It works great....except... In column \$M\$3:\$M\$189 there are also blank cells and I want to count these a...

SUMPRODUCT and OR?
How do you count rows from criterias in two columns where the criteri shall be OR? I.e. something lik SUMPRODUCT(('[jisses.xls]Requirements'!\$E\$2:\$E\$10000="Car")+('[jisses.xls]Requirements'!\$F\$2:\$F\$10000="Car")) but where you get a count on number of rows where either (both column are = Car) or (any of the columns are = Car)? -- Message posted from http://www.ExcelForum.com Rune, Try =SUMPRODUCT(('[jisses.xls]Requirements'!\$E\$2:\$E\$10000="Car")+('[jisses.xls]R equirements'!\$F\$2:\$F\$10000="Car"))-SUMPRODUCT(('[jisses...

Convert (change) Functional Currency
We have a very serious issue where we wish to convert (change) the functional currency of a GP system that has been in production for a very long time => has a lot of data accumulated. As far as I know this can not be done by any GP functions/utilities. Does anyone know of any ways that we could go about doing this or any third party products or any leads at all that we could follow? Any help will be greatly appreciated. Thanks, Elias Hi Elias, Unfortunately, I guess you are right on GP not having an out-of-box solution for this. In the past on one occasion I had to conver...

Macro or Function to make text size to suite text Length?
I would like to find Macro or Function that will make text size to suite text Length? eg. if the first cell has "bill smith" then the text size would be 10. But if you had "Christina Vandermear" then the text size would be as small as 7 in order to fit in that same size cell. I know it's a bit of an odd request, but is there a way to do that? thanks Hi only possible with VBA. You may download the following addin for this extension of the conditional format (as Excel's build in format can't change the font size): http://www.xldynamic.com/source/xld.CF...

Help Debug Complex Formula (SUMPRODUCT? SUMIF?)
Folks, I'm really struggling with this one. I've got 354 rows of data (rows 3:352). In column G there may be a date. In column M there is a number (1-12) which represents a monthly period, and in column O there is a dollar amount which represents a montly invoice total. I need to construct a formula which calculates the sum of O for a specific period M where there is a date entered (non-blank cell) in G. The formula below is what I constructed but it does not work. Rather it calculates the sum of O for the specified period in M but then multiplies the sum (\$16,200) by the num...

Sumproduct or ?????
Hi All, I have 2 tables one is cust table and the other one is amount table. A B C D Customer Table Amount Table Parent ID Cust # Cust # Amount 2001 AA AA 5 2001 BB AA 5 2001 CC AA 5 2001 DD AA 5 2001 EE AA -5 2001 FF AA -5 2001 GG BB 3 BB 3 BB -3 Summary AA - I want to be able to count if "AA" in Cust table (Column B) then count positive amount minus negative amount in amount table (Column D). In this case the answer is "2" BB - The same thing with "BB". The answer is "1" Thank you ...

Problem writing Time-Out function
Alright this one might seem a little odd. Basically when I initialize my dialog I have to call a routine (RunSameThread) which unfortunately calls some functions set with a library (which I don't have the source code for) which may not exit. (Basically this call should initialize the COM port and send's some commands to a transceiver attached to the port, then wait for a response before returning). Now, if the transceiver is not hooked up (or turned on) the COM port initialization will go though, but the library function will sit in an infinite loop :( Thus, I am trying to write a t...

Increasing the speed of Sumproduct
Hi, 1. Just read http://www.mcgimpsey.com/excel/formulae/doubleneg.html In this JEM says that we have double negs so that =SUMPRODUCT(--(A1:A5>10),B1:B5)) can be coerced in to 1. As per JEM"s explanation single unary will coerce True/False to Zero/One and the second double unary is used so that the negative values could be converted to its original sign. My "reasoning" was instead of using double negative sign why not use a single + sign and achieve further speed increase. So I did this =SUMPRODUCT(--(A1:A5>5),--(A1:A5<10),B1:B5) . The data I used was ...

find function #4
I'm trying to do a similair process to the lookup function, but the dat isn't sorted, and due to some cross sheet links, I am unable to sor the data. So I'm looking for a function to find the correct company i one range and then return the correct multiplier in the correspondin range... I will comment my function with missing code Function FindReturn(Company) Set Foundcell = ActiveSheet.Range("B62:B69").Find_ What:="end", LookIn:=xlValues, LookAt:=xlWhole) If Foundcell Is Nothing Then FindReturn = 1 Else r = Foundcell.Row ' FindReturn = value in cell wit...

Excel Calculator always requires an = before a math function, can be turned off setting?
I often use excel as a fancy calculator. But often I forget to start the calculator with the = sign. So if I want to add 44 + 66, I forget to type in an = sign first, an it gives me an error message. Is there a setting to turn that off, so I can type in 44 + 66 and it gives me an answer. I always have Excel running and it's also my calculator. I never use use a calculator anymore! The answer to your question is no. The equal sign tells Excel that you're entering a formula that needs to be calculated. You can use a plus sign (+) instead of the equal sign (=) if you alwa...

Plotting functions
I am brand new to using the charting feature of Exel and am having trouble with what should be a simple operation. I want to create a graph of y=f(x) -- say for simplicity's sake f(x) = x^2. I have in column A, for x, the numbers -10 through +10 and in column B =A^2. Column B shows the correct values but have not been able to create the pretty graph I know so well. Would someone give me a bit of help here? Thank you very much. Where do you get stuck? Select the data range, click the Chart Wizard. In step 1 select the XY Scatter chart. That should give you what you want. ...

Disabling automatic send function
I used to be able to have messages in my Outbox sit there till I hit send/receive by checking "disable send/receive" on the tools menu. For some reason, disabling send/receive now only disables the automatic receive function. My emails in my Outbox are sent automatically, as soon as they hit the outbox. I send monthly emails to my contacts thru the mail merge program in Word. I then go to my Outbox to attach a document to them. It used to be that if I disabled the automatic send/receive function in the tools menu, the messages in my outbox would sit there so I could add ...

Sumproduct??
Hi I have a range A1:S1 with a series of integers and a range A2:S2 with another series of integers. How can I count all the instances where the equivalent cell in the second range is equal to two less than that in the first range. eg 6 4 9 7 8 etc 4 3 7 8 7 etc the above would count 2 - Columns A and C Thanks in advance Sandy =SUMPRODUCT(--(A1:S1-A2:S2=2)) =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail here: http://www.xldynamic.com/source/...

Can a VBA Function be used to execute (call) a Macro?
Does anyone know if a VBA Function can be used to execute (call) a Macro? How would you do this? A VBA function IS a macro. What exactly are you trying to do? On Tue, 23 Nov 2004 06:11:02 -0800, RKGriffin <RKGriffin@discussions.microsoft.com> wrote: >Does anyone know if a VBA Function can be used to execute (call) a Macro? How >would you do this? I am using the "Function," below, which appears to be different than a "Sub" Routine type Macro, because in a sub-routine I can call another sub-routine and in this thing, I can't. Actually, all I want thi...

ISBLANK() function
Hi. I need some help. I have two columns in a sheet, A1:A100 and B1:B100, in which it is: The first column A1:A100, have some data (numbers). In the second column apply B1=A1, B2=A2,……..B100=A100. In the B101 i am trying to apply the SUM(B1:B100), but only if the cells A1:A100 are not empty (the zero value must be considered as number, not empty cell). In the case, which the cells A1:A100 are empty, i would like to not print anything in B101 cell. I tried the use of ISBLANK() function {IF(ISBLANK(B1:B100)=FALSE;SUM(B1:B100);"")}, but when the A1:A100 cells are empty then th...

Block Sender function
I get e-mails from this Radio Guanaca several times a day. I click on Message, Block Sender, but e-mail keeps coming in, and it's the exact same e-mail address/domain, whatever every time. Why isn't this sender being blocked? Thanks! no more radio suezeee ...

sumproduct
I have this formula in use and it works perfectly. I now need to change the sum area from H4:h484 to H4:J484 and now it only give me 0. How do I get it to sum the larger area?? =IF(\$B3<=0,"",SUMPRODUCT((SVEexp!\$B\$4:\$B\$484=Summary!\$B3)*(SVEexp!\$C\$4:\$C\$48 4=Summary!\$C3),SVEexp!\$H\$4:\$H\$484)) Thank you, Jerry Hi You need to make sure that all of the ranges in SUMPRODUCT are the same size. If you alter one of the ranges, you'll have to alter the others accordingly. Hope this helps. Andy. "Jerry Kinder" <jkinder@jkinder.com> wrote in message news:eeznF54...

Domain/Forest Functional Level
I have a Windows 2003 Forest with a Forest Root Domain, and 3 child domains. Can I upgrade the forest root to windows 2008 functional level but leave the other domains at 2003? What do I need to do with the Forest functional level? I would like to take advantage of the enhanced features of DFS on W2k8 R2 but I need the domain functional level to be upgraded. CK wrote: > I have a Windows 2003 Forest with a Forest Root Domain, and 3 child > domains. Can I upgrade the forest root to windows 2008 functional > level but leave the other domains at 2003? What do I need to do wit...