|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
July 28th, 2004, 08:44 PM
|
|||
|
|||
sumproduct question
I have three columns. They go like this:
100 1 2 200 0 2 100 1 0 200 0 2 100 1 2 200 0 0 I've always been under the impression that a + sign represents OR. When I try this formula: =sumproduct(((C1:C60)+(B1:B60))*A1:A6) I get 900. I want it to equal 700, because I want the formula to say if column B OR column C is greater than 0, then sum column A. I must be doing something wrong. What is it? Help is appreciated!!! 
|
|
#2
July 28th, 2004, 08:52 PM
|
|||
|
|||
|
sumproduct question
Hi
use =sumproduct(--((C1:C60)+(B1:B60)0),A1:A6) -- Regards Frank Kabel Frankfurt, Germany mark1 wrote: I have three columns. They go like this: 100 1 2 200 0 2 100 1 0 200 0 2 100 1 2 200 0 0 I've always been under the impression that a + sign represents OR. When I try this formula: =sumproduct(((C1:C60)+(B1:B60))*A1:A6) I get 900. I want it to equal 700, because I want the formula to say if column B OR column C is greater than 0, then sum column A. I must be doing something wrong. What is it? Help is appreciated!!!
|
|
#3
July 28th, 2004, 08:55 PM
|
|||
|
|||
|
sumproduct question
Mark,
That is because the rows where both are 0 is double counting, that is you get an extra 100 twice. Try this =SUMPRODUCT(--(((C1:C60)+(B1:B60))0),A1:A6) -- HTH ------- Bob Phillips "mark1" wrote in message ... I have three columns. They go like this: 100 1 2 200 0 2 100 1 0 200 0 2 100 1 2 200 0 0 I've always been under the impression that a + sign represents OR. When I try this formula: =sumproduct(((C1:C60)+(B1:B60))*A1:A6) I get 900. I want it to equal 700, because I want the formula to say if column B OR column C is greater than 0, then sum column A. I must be doing something wrong. What is it? Help is appreciated!!!
|
|
#4
July 28th, 2004, 09:51 PM
|
|||
|
|||
|
sumproduct question
Hello Frank. Hope you are doing well today.
I understand that if you use the double-negative, a comma can be substituted for your multiplication sign. What about the addition sign? Is there a substitute for that? -----Original Message----- Hi use =sumproduct(--((C1:C60)+(B1:B60)0),A1:A6) -- Regards Frank Kabel Frankfurt, Germany mark1 wrote: I have three columns. They go like this: 100 1 2 200 0 2 100 1 0 200 0 2 100 1 2 200 0 0 I've always been under the impression that a + sign represents OR. When I try this formula: =sumproduct(((C1:C60)+(B1:B60))*A1:A6) I get 900. I want it to equal 700, because I want the formula to say if column B OR column C is greater than 0, then sum column A. I must be doing something wrong. What is it? Help is appreciated!!! .
|
|
#5
July 28th, 2004, 10:00 PM
|
|||
|
|||
|
sumproduct question
Hi
no there's no similar substitution for this. -- Regards Frank Kabel Frankfurt, Germany mark1 wrote: Hello Frank. Hope you are doing well today. I understand that if you use the double-negative, a comma can be substituted for your multiplication sign. What about the addition sign? Is there a substitute for that? -----Original Message----- Hi use =sumproduct(--((C1:C60)+(B1:B60)0),A1:A6) -- Regards Frank Kabel Frankfurt, Germany mark1 wrote: I have three columns. They go like this: 100 1 2 200 0 2 100 1 0 200 0 2 100 1 2 200 0 0 I've always been under the impression that a + sign represents OR. When I try this formula: =sumproduct(((C1:C60)+(B1:B60))*A1:A6) I get 900. I want it to equal 700, because I want the formula to say if column B OR column C is greater than 0, then sum column A. I must be doing something wrong. What is it? Help is appreciated!!! .
|
|
#6
July 28th, 2004, 10:14 PM
|
|||
|
|||
|
sumproduct question
The double unary is not what determines whether a comma or an asterisk can
be used. The comma is only used where there is not a need to coerce the evaluated array to an array of 1/0. You can use an asterisk here, but that means that the whole formula is evaluated to a single array before the SUMPRODUCT kicks in, as against there being 2 (or more) arrays if you use the comma. You can get an explanation at http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH ------- Bob Phillips "mark1" wrote in message ... Hello Frank. Hope you are doing well today. I understand that if you use the double-negative, a comma can be substituted for your multiplication sign. What about the addition sign? Is there a substitute for that? -----Original Message----- Hi use =sumproduct(--((C1:C60)+(B1:B60)0),A1:A6) -- Regards Frank Kabel Frankfurt, Germany mark1 wrote: I have three columns. They go like this: 100 1 2 200 0 2 100 1 0 200 0 2 100 1 2 200 0 0 I've always been under the impression that a + sign represents OR. When I try this formula: =sumproduct(((C1:C60)+(B1:B60))*A1:A6) I get 900. I want it to equal 700, because I want the formula to say if column B OR column C is greater than 0, then sum column A. I must be doing something wrong. What is it? Help is appreciated!!! .
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Sumproduct or Countif question --- multiple criteria | Ken Wright | Worksheet Functions | 4 | October 28th, 2003 10:08 AM |
Linear Mode
