"TRUE" "TRUE" formula needed

 Hello,  I have Excel 97.  I have this in my worksheet:

                                         new column
      A      B       C         D        E
 1  50    25    TRUE      5      TRUE
 2  20    30    FALSE    30     FALSE
 3  80    60    TRUE     -20    TRUE
 4  10    40    FALSE   -15     TRUE
 5  35    25    TRUE      5      TRUE
 6  40    30    TRUE    -15     FALSE
 7  10    15    FALSE

I was previously sent the following formula which worked great for finding every "TRUE" in column C and calculating the values in column D next to every "TRUE" in column C.
=SUMPRODUCT(--(C1:C7=TRUE),--(D1:D7>0),D1:D7)  for positive TRUE
=SUMPRODUCT(--(C1:C7=TRUE),--(D1:D7<0),D1:D7)  for negative TRUE

Now I need to do the same as above but add column E into the mix.  What do I add to the above formulas? I want to end up with only all TRUE's in columns C & E.  One formula for positive values and one for negative as above.
Thanks again for your time!
Sam

0
anonymous (74721)
4/15/2004 5:46:03 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
400 Views

Similar Articles

[PageSpeed] 38

Hi Same
shouldn't be that complicated :-)
try
=SUMPRODUCT(--(C1:C7=TRUE),--(D1:D7>0),--(E1:E100=TRUE),D1:D7)
and
=SUMPRODUCT(--(C1:C7=TRUE),--(D1:D7<0),--(E1:E100=TRUE),D1:D7)

--
Regards
Frank Kabel
Frankfurt, Germany


Sam wrote:
> Hello,  I have Excel 97.  I have this in my worksheet:
>
>                                          new column
>       A      B       C         D        E
>  1  50    25    TRUE      5      TRUE
>  2  20    30    FALSE    30     FALSE
>  3  80    60    TRUE     -20    TRUE
>  4  10    40    FALSE   -15     TRUE
>  5  35    25    TRUE      5      TRUE
>  6  40    30    TRUE    -15     FALSE
>  7  10    15    FALSE
>
> I was previously sent the following formula which worked great for
> finding every "TRUE" in column C and calculating the values in column
> D next to every "TRUE" in column C.
> =SUMPRODUCT(--(C1:C7=TRUE),--(D1:D7>0),D1:D7)  for positive TRUE
> =SUMPRODUCT(--(C1:C7=TRUE),--(D1:D7<0),D1:D7)  for negative TRUE
>
> Now I need to do the same as above but add column E into the mix.
> What do I add to the above formulas? I want to end up with only all
> TRUE's in columns C & E.  One formula for positive values and one for
> negative as above. Thanks again for your time!
> Sam

0
frank.kabel (11126)
4/15/2004 5:53:28 AM
Hello Sam

One way:

=SUMPRODUCT(--(C1:C7=TRUE),--(E1:E7=TRUE),--(D1:D7>0),D1:D7)  for positive
TRUE
=SUMPRODUCT(--(C1:C7=TRUE),--(E1:E7=TRUE),--(D1:D7<0),D1:D7)  for negative
TRUE

another way

=SUMPRODUCT((C1:C7=TRUE)*(E1:E7=TRUE)*(D1:D7>0)*D1:D7)  for positive TRUE
=SUMPRODUCT((C1:C7=TRUE)*(E1:E7=TRUE)*(D1:D7<0)*D1:D7)  for negative TRUE

-- 
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Sam" <anonymous@discussions.microsoft.com> skrev i en meddelelse
news:3AA655F9-9D7B-4E71-84E9-FDD758400BFF@microsoft.com...
> Hello,  I have Excel 97.  I have this in my worksheet:
>
>                                          new column
>       A      B       C         D        E
>  1  50    25    TRUE      5      TRUE
>  2  20    30    FALSE    30     FALSE
>  3  80    60    TRUE     -20    TRUE
>  4  10    40    FALSE   -15     TRUE
>  5  35    25    TRUE      5      TRUE
>  6  40    30    TRUE    -15     FALSE
>  7  10    15    FALSE
>
> I was previously sent the following formula which worked great for finding
every "TRUE" in column C and calculating the values in column D next to
every "TRUE" in column C.
> =SUMPRODUCT(--(C1:C7=TRUE),--(D1:D7>0),D1:D7)  for positive TRUE
> =SUMPRODUCT(--(C1:C7=TRUE),--(D1:D7<0),D1:D7)  for negative TRUE
>
> Now I need to do the same as above but add column E into the mix.  What do
I add to the above formulas? I want to end up with only all TRUE's in
columns C & E.  One formula for positive values and one for negative as
above.
> Thanks again for your time!
> Sam
>


0
4/15/2004 5:59:18 AM
"Leo Heuser" <leo.heuser@NOSPAMadslhome.dk> wrote in message
news:OR5pE7qIEHA.3556@TK2MSFTNGP10.phx.gbl...
> Hello Sam
>
> One way:
>
> =SUMPRODUCT(--(C1:C7=TRUE),--(E1:E7=TRUE),--(D1:D7>0),D1:D7)  for positive
> TRUE
> =SUMPRODUCT(--(C1:C7=TRUE),--(E1:E7=TRUE),--(D1:D7<0),D1:D7)  for negative
> TRUE
[...]

Leo,

Why not dispense with the equality test...

=SUMPRODUCT(--C1:C7,--E1:E7,--(D1:D7>0),D1:D7)
=SUMPRODUCT(--C1:C7,--E1:E7,--(D1:D7<0),D1:D7)

Aladin


0
akyurek (248)
4/15/2004 7:33:56 AM
Reply:

Similar Artilces:

Problems with DataEntry = True
Hi I am opening a form "Add New Job", which on load, sets its DataEntry attribute to true. If I enter no details & close the form, a record had been created, so I added code to delete the record if no details had been entered. Now when I close the form without entering any details, it deletes the current record it has just created, but creates another one with the next sequence number. What I want it to do is create no record if no details are added. How can I achieve this? Yours Stapes Are you sure it's deleting the record you just created? The DataEntry property does...

True to TRUE
I'm entering data for a database at work and when I type in the last name "True" Excel is changing it to "TRUE." I try to just undo it and it won't. How do I make it so it's just True???? TRUE is a special value in Excel. To enter True, precede the text with an apostrophe. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Nikki@CustomMortgageSolutions" <Nikki@CustomMortgageSolutions@discussions.microsoft.com> wrote in message news:E35515F3-F2E8-4B05-B970-66951A7600A4@microsoft.com......

True Justify Text in multivalued ComboBox
Hi, Is there a way to true justify the text in multivalued ComboBoxespecially on the report (Access 2007)? Thanks in advance, Kozaw ...

Cancel = true
Hi I am attempting to run the following: - Dim stDocName As String stDocName = "KPI Data - New links to PEOPLE table" DoCmd.OpenReport stDocName, acPreview stDocName = "KPI Data - New ATTEND Records" DoCmd.OpenReport stDocName, acPreview Each report has the On No Data property set, displays a message, then sets Cancel = True. However, it appears it doesn't do anything else after Cancel is set to True in the first report. It does not even try to open the second report. I guess this is because Cancel is set to True. How can I unset this - if that is th...

TRUE vs true or FALSE vs false
What exactly is the difference. This has always bothered me that this is the case. I've noticed that you cannot substitute one for the other. I'm guessing TRUE was the original way of doing things before "true" came along? The same case can be made with FALSE vs false. Thanks On Wed, 16 Jun 2010 09:23:44 -0500, "JCO" <someone@somewhere.com> wrote: >What exactly is the difference. This has always bothered me that this is >the case. I've noticed that you cannot substitute one for the other. I'm >guessing TRUE was th...

Formula is true if proportion of range is true
Trying for true / yes result if at least 50% of range meets criteria eg =IF(AND O2="yes",H2<>"",I2<>"",J2<>"",K2<>"",L2<>"",M2<>"",N2<>""),"yes","no") where O2 must be yes and input is required for at least 4 out of 7 cells between H2 and I2 thank you =IF(AND(O2="yes",COUNTA(H2:N2)>3),"True","False") Vaya con Dios, Chuck, CABGx3 "nussbaum" <nussbaum@discussions.microsoft.com> wrote in message news:F7...

Is it true? Word Merge
Is it true that you cannot do a simple merge document directly from MS CRM??? The video I found indicated that you needed to export to excel and then merge with Word? I need the ability to have single letters that can be easily merged with a single contact by end users. I would have thought this would be a base functionality for any CRM but it appears this is not the case. Is there a way to do this? I am loving everything else I am seeing about MSCRM but this is a fundemental requirement and may be a show stopper for us. Could someone please comment. I find it hard to believe t...

true-false
I want to select a field when the condition (other fields in the same are true) -- Message posted via http://www.accessmonster.com You're really going to have to give us a little more explanation of what you're trying to do/what your problem is for anyone here to help! anthonyd wrote: >I want to select a field when the condition (other fields in the same are >true) -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000 Message posted via http://www.accessmonster.com On Sun, 25 Mar 2007 16:02:01 GMT, "anthonyd via AccessMonster.co...

How do I stop Excel from changing the word true to TRUE?
I am typing names and addresses into an Excel spreadsheet. I have one person whose first name is True. When I type it into the cell, it converts it to TRUE. I know this is a function/formula, but how do I stop it from doing that? Try this: Either put an apostrophe before the name: 'True Or Format>Cells>Number Category: TEXT Does that help? *********** Regards, Ron "Schmyerlou" wrote: > I am typing names and addresses into an Excel spreadsheet. I have one person > whose first name is True. When I type it into the cell, it converts it to > TRUE. I k...

"TRUE" "TRUE" formula needed
Hello, I have Excel 97. I have this in my worksheet: new column A B C D E 1 50 25 TRUE 5 TRUE 2 20 30 FALSE 30 FALSE 3 80 60 TRUE -20 TRUE 4 10 40 FALSE -15 TRUE 5 35 25 TRUE 5 TRUE 6 40 30 TRUE -15 FALSE 7 10 15 FALSE I was previously sent the following formula which worked great for finding every "TRUE" in column C and calculating the values in column D next to every "TRUE" in column C. =SUMPRO...

Count condition true for 3 lookup columns
Hi All, I have a worksheet that contains 3 seperate lookup columns. My challenge is that I need a low overhead way to find and count the combination of true lookups. So if one of the 3 looksup is found I would get a return of '1', if 2 of the 3 are found then '2'. I know I can do this with an array formula, but am worried about the calculation hit as this is a very complex worksheet and already has a long intitial calc time. I suppose I could do the inverse and could an 'iserror' condition and then subract that answere from 3. I thought of using a complex 'if&...

TRUE: Would like it to appear as "True" in the cell.
Using Microsoft Excel 2007. I am typing in a name 'True', but when I type the name it is capitalizing the entire word 'TRUE'. I would like to capitalize only the first letter in the name. How do I achieve this using Excel? I believe Excel recognizes it as a formula. Either: format the cell to Text prior to entry or: precede the value with an apostophe (single quote) -- Gary''s Student - gsnu201001 "Lindsay" wrote: > Using Microsoft Excel 2007. > > I am typing in a name 'True', but when I type the name it is cap...

Format a column for "True / False"?
Having come from Access I am used to creating a field (column) which can be a "yes\no" or "true\false" but which actually contains "0" or "-1" Is it possible to do this in excel, and is it possible to have a check box on the worksheet which will enter the value for me regards Roga Just use TRUE/FALSE in the column. If you pick a checkbox from the controls toolbox, and link that to a cell, that sets the cell value to TRUE/FALSE. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "roga" <...

Cancel = True
Hello All I have a number of reports where I have set the OnNoData event to Cancel = True. This works fine where the user attempts to open one of the report usign a button created with the wizard for that purpose, but if they click a button where the OnClick event includes lots of DoCmd OpenReport commands, when the code reaches a report with no data they get a message that the OpenReport was cancelled - and the code stops. What do I do? Hope someone can help Many thanks Leslie Isaacs Do you have error handling in your code? IF so, does it use Resume Next? We can'...

How do I get a true calculation?
Please explain in somewhat more detail what your requirement is. -- Kind regards, Niek Otten "reckyroo" <reckyroo@discussions.microsoft.com> wrote in message news:5577D52D-BFE3-4355-BC34-48F015A613E4@microsoft.com... | ...

Excel: with an if condition display a row of cell if true
Hello, I am a relatively new user and I was asked to set up a contact list for my office. I would like to have one master list on the first worksheet and then kind of query different sections into new worksheets. Is this possible? For example in the main worksheet i would have name, region and supervisor. In the second worksheet I would want only Peel Region enteries to be displayed. The main goal is: If a change something on the first worksheet I would like the other worksheets altered automatically. PLEASE HELP ME. Copy the data that you want from the main worksheet and then paste...

Countif( 2 criteria = true ?)
Hi can you please help me... I am trying count the number of items based on 2 criteria can you please help me..? Basically I have a sheet and in col A I have Code and col B I have name. eg: Col A - Col B 1001 - Mike 1001 - Dave 1001 - Keith 1002 - Terry 1001 - Mike What I am trynig to do is count how many say ("Mike" col B) with code of say ("1001" col A). so the answer according to the above will be "2". I know I can use the dsum function to do this but the problem is I am trying to get a formula in 1 cell to do the job as opposed to 2 rows + 3 columns.. he...

What's the best way to toggle between true and false in Excel? Hi all, My excel work involves a lot of toggling between true and false (boolean types) ... and it's very repetitive... Is there a w
What's the best way to toggle between true and false in Excel? Hi all, My excel work involves a lot of toggling between true and false (boolean types) ... and it's very repetitive... Is there a way to select a bunch of cells, and press a key short-cut so that they toggle all at once? Thanks! I can't tell you whether this is a "best way" to do what you want or not, but it is "a way" to do it. It doesn't use a key short-cut, rather it uses a right mouse click. Go into the VB editor and double click on ThisWorkbook in the Project window, then copy/paste...

copying true formulas only
I want to create a new row and copy selectively from the one above. I want to copy formats and formulas but not values. I am using Excel 2002. I can use insert to create a new row with all the same formats (including conditional formatting). If I then use paste-->special-->formulas Excel pastes both formulas AND values of the cells which contain no formula. I want to avoid writing something which tests each cell to see if it contains a true formula. Any suggestions anyone? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and ...

Want to write formula
Hello I want an if which checks two conditions If it was in C++ would look like this: if(H3 == "TRUE") || (B2 < B3) //update cell contents to "TRUE" The || is like an OR - ie can be this or that. Hopw can I do this in Excel VBA? Angus Hello Angus, If (Range("H3").text = "TRUE") or (Range("B2").value < Range ("B3").value) then .... Regards, Bernd =IF(OR(H3=TRUE,B2<B3,TRUE,FALSE) This can be simplified to OR(H3,B2<B3) Not I have not use quotes as in "TRUE" since you can type TRUE into a cell an it is...

if, or, true, false?
I want to create a delinquency report using info from multiple worksheets. I have an account column and next to that I want to create a deliquent column. In the individual worksheets there is a date column. I want excel to look at the cells in that column and return a deliquent message if no date is present. Do you mean =IF(COUNTA(Sheet2!A:A)=0,"delinquent","no") -- HTH RP (remove nothere from the email address if mailing direct) "bdq17" <bdq17@discussions.microsoft.com> wrote in message news:5B3AA700-0A0E-41CE-8FB0-2CBAE6C90265@microsoft.com... &...

Incrementing if TRUE
I have a formula which analyses the data each time an entry is made in a particular cell and returns the value TRUE or FALSE. I wish to count the number of times the value TRUE is returned by imcrementing the value in another cell by 1 each time the data entered equates to TRUE. I have been trying to achieve this without using a Macro using the IF command, {If((B4 = TRUE), A4 = A4+1, A4)} but have drawn a blank on how to make this work. Can anyone help with what must be a simple and common enough task please. Peter Peter, If you goto Tools>Options and on the Calculation tab...

AMAZING BUT TRUE
Please take 5 minutes to read this article: IT WILL CHANGE YOUR LIFE, just like it did mine. It's true! You can make upto or over $50,000 in 4 to 6 weeks, maybe sooner! I swear I'm not lyingto you, and this is not a scam! If your interested, read further; and if not , then don't part- icipate. However, please download this , print it out and give it to someone else who would like to take advantage. Here we go. A little while back, I was browsing through these news- groups, just like you are doing now, and came across an article similar to this, that said you could make thousand...

Is this true with KB926666?
Hi all, For all wroktations that have exchange system manager installed, we have to apply KB926666. Is this true? what about the blackberry server? Thanks. On Wed, 7 Mar 2007 07:27:13 -0800, Jane <Jane@discussions.microsoft.com> wrote: >Hi all, > >For all wroktations that have exchange system manager installed, we have to >apply KB926666. Is this true? what about the blackberry server? > >Thanks. Exchange Service pack and hotfix levels should always match regardless. on all machines that access the Exch Servers. KB 926666 is the CDO patch for Exchange Ser...

How to return a number instead of true/false with an if function?
Please help =IF(your condition,1,2) eg =IF(A1=B1,1,2) will return a 1 if TRUE, and a 2 if FALSE -- HTH Kassie Replace xxx with hotmail "charles" wrote: > Please help Always elaborate on your issue in the message area (that big white space). The subject line's just that, meant to be brief keynotes on your issue. It's NOT your query. Elaborate by posting your formula or your formula attempts and by describing details of what you have (your sheet set-up, sample data) and what you want to do/happen Anyway, here's 2 simple IF examples to groove you in its use ...