making a sum outcome negative based on adjacent cell value

I have 3 columns of single figures. At present i'm using the  sumproduc
fuction to multiply and total the figures in column A that fall betwee
4 and 9 with the adjacent figure in column B...

=SUMPRODUCT(--($A1:$A600>=4),--($A1:$A600<=9),($B1:$B600),($A1:$A600)

I'd like to add column C to the formula, so that if it contained 
value of -1, 1 or 2, the sum of the adjacent figures in columns A and 
appears as a negative number.

For example

A3= 7, B3= 2, C3= 1      Outcome= -14

A4= 9, B4= 1, C4= 5       Outcome=  9

A5= 3, B5= 2, C5= 2       No sum because figure in column A       doe
not fall between 4 and 9.

Can anyone help

--
Message posted from http://www.ExcelForum.com

0
7/2/2004 6:28:00 PM
excel 39879 articles. 2 followers. Follow

3 Replies
384 Views

Similar Articles

[PageSpeed] 22

Try the same method you used below or

=SUMPRODUCT(--($A1:$A600>=4),--($A1:$A600<=9),($B1:$B600),($c1:$c600={-1,1,2
}),($A1:$A600)

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"judoist >" <<judoist.18s4cm@excelforum-nospam.com> wrote in message
news:judoist.18s4cm@excelforum-nospam.com...
> I have 3 columns of single figures. At present i'm using the  sumproduct
> fuction to multiply and total the figures in column A that fall between
> 4 and 9 with the adjacent figure in column B...
>
> =SUMPRODUCT(--($A1:$A600>=4),--($A1:$A600<=9),($B1:$B600),($A1:$A600)
>
> I'd like to add column C to the formula, so that if it contained a
> value of -1, 1 or 2, the sum of the adjacent figures in columns A and B
> appears as a negative number.
>
> For example
>
> A3= 7, B3= 2, C3= 1      Outcome= -14
>
> A4= 9, B4= 1, C4= 5       Outcome=  9
>
> A5= 3, B5= 2, C5= 2       No sum because figure in column A       does
> not fall between 4 and 9.
>
> Can anyone help?
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
Don
7/2/2004 7:30:45 PM
Are you sure? Tried that and keep getting #VALUE

--
Message posted from http://www.ExcelForum.com

0
7/2/2004 8:35:25 PM
I know there's a better way.
I just can't think of it right now.

=SUMPRODUCT(($A1:$A600>=4)*($A1:$A600<=9)*($B1:$B600)*($A1:$A600))-(SUMPRODU
CT(($A1:$A600>=4)*($A1:$A600<=9)*(C1:C600={-1,1,2})*($B1:$B600)*($A1:$A600))
)*2
-- 

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"judoist >" <<judoist.18sa8z@excelforum-nospam.com> wrote in message
news:judoist.18sa8z@excelforum-nospam.com...
Are you sure? Tried that and keep getting #VALUE!


---
Message posted from http://www.ExcelForum.com/

0
ragdyer1 (4060)
7/2/2004 10:08:27 PM
Reply:

Similar Artilces:

Category color changes when changing values.
When I copy a chart I get two identical ones. When I change the values of one of the charts and sort the values, Excel changes the colors of the categories to a preset order, so that the color of the biggest customer in chart 1 is not the same as this same customer (let's say now on the third place) in chart 2. Is there a way to prevent this? ...

How make range of Hyperlinks?
I see how to make a single cell into a Hyperlink but what if we want to have all email and web addresses in a spreadsheet turn into hyperlinks? Is there an easy way to turn this on and off? Hi for making hyperlinks in your selected range have a look at the following macro: Sub MakeHyperlinks() Dim cell As Range For Each cell In Intersect(Selection, _ Selection.SpecialCells(xlConstants, xlTextValues)) With Worksheets(1) .Hyperlinks.Add Anchor:=cell, _ Address:=cell.Value, _ ScreenTip:=cell.Value, _ TextToDisplay:=cell.Value End Wit...

Can I make a database in Word with Excel Data
I know this may be a "DUH" question, but... I am making a list of area dentists that I have copied and pasted of of the Dex website into Excel. I am wondering if I can take this inf and make a database in Word for printing address labels. Does anyon know if this can be done, and HOW?:confused -- Myn77 ----------------------------------------------------------------------- Myn777's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2958 View this thread: http://www.excelforum.com/showthread.php?threadid=49283 For help on Word mail merge using Excel as the...

How to see calculation and heading in same cell.
I would like the cell to perform a calculation and then display the answer as will as a heading. In other words the answer and the heading will appear in the same cell. Perhaps you mean something like ="Heading Name: "&A1+B1 ******************* ~Anne Troy www.OfficeArticles.com "Jeracho" <Jeracho@discussions.microsoft.com> wrote in message news:F34BACB9-E2DA-448A-924D-46A475F98F91@microsoft.com... > I would like the cell to perform a calculation and then display the answer as > will as a heading. In other words the answer and the heading will appear in...

How can I Enable a Check Box based on another fields value?
Hi There, I have a form with a disabled check box. I need to enable it when a certain value ("approved") is selected from a combo box. When I am in Form Design View and I have the Check Box selected the Conditional Formatting menu item on the Format menu is greyed out. I am using Access 2003. Can you tell me what I need to do to make this work? Many thanks, David As you've discovered, Conditional Formatting isn't availabe to checkboxes. Try this: Private Sub Form_Current() If Me.YourComboBox = "Approved" Then YourCheckBox.Enabled = True Else YourChe...

Newbie: can't get a calculated value on the form?
I have a table with numbers and a form that shows the numbers. I have a query that takes one of the numbers and mulitplies it. I put a text box on the form from the query result field, but I get a "#Name" error instead of the result. When I run the query, I get the correct result. Help, please? Ed "Ed from AZ" <prof_ofwhat@yahoo.com> wrote in message news:d1e7d27c-11d9-4696-8d19-4c5fdd9dbb89@d70g2000hsb.googlegroups.com... >I have a table with numbers and a form that shows the numbers. I have > a query that takes one of the numbers and mulitplies it. I p...

File Association in Dialog-Based Application
Has anyone seen any info on how to associated a file type with my dialog-based application? In order to use RegisterShellFileTypes(), I must add my document template using AddDocTemplates(). But AddDocTemplates() can't be called without a CDocument class. Thanks for any thoughts. -- Jonathan Wood SoftCircuits Programming http://www.softcircuits.com >Has anyone seen any info on how to associated a file type with my >dialog-based application? > >In order to use RegisterShellFileTypes(), I must add my document template >using AddDocTemplates(). But AddDocTemplates() ...

Graph Data Values
I am trying to create a pick and mix graph that shows forecast spen against actual spend by a selected business area. I somehow want to create a graph on the fly based on the selections fo example: If I choose company one and want to see the contracted data I want th graph to pick up the forcasted contracted Labour, Passthru and Othe and show the combined contracted actuals; See attached; and if i then choose company 2 then graph will pick up the releven ranges? HELP!! Attachment filename: help.zip Download attachment: http://www.excelforum.com/attachmen...

Converting Values to Unicode Characters
The function CHAR converts a value in the range 1-255 to to an ANSI character. Is there a way to convert values in the range 1-65,342 to Unicode characters? -- Gary L. Smith gls432@yahoo.com Columbus, Ohio You can use VLOOKUP but you'll have to create your own lookup table of the unicode characters and I think you'll have to paste it onto the same spreadsheet.... Maybe you can find a lookup table on the internet somewhere that you can easily paste into your spreadsheet. tsides <tsides@intelligentsystemsconsulting.com> wrote: > You can use VLOOKUP but y...

Coping part of a cell content into a seperate cell
Hi I have two cells, one containing first and middle name and another one with surname. I want to combine the first name and surname into a separate cell, can you advise how I can just copy the first name and miss out the middle name please?? Thanks Caz Hi, I assume that the midle name is separated by a space from the first name and is in column A and the last name in column B =TRIM(LEFT(A2,FIND(" ",a2)-1))&" "&B2 "Caz H" wrote: > Hi > I have two cells, one containing first and middle name and another one with &g...

Combo box choice outcome
I have a combo box with the following choices: Checks_Walkthrough Checks_Monitor Research_Case Inventory_Maint Task_Cable Task_Misc New Issue What I want is if anything BUT "New issue" is selected, two fields in the same form as the combo box are populated by other data in the table the above list is pulled from. I can get this to happen by itself. Me.Text27.Value = Me.imac.Column(2) Me.model.Value = Me.imac.Column(3) If "New Issue" is selected, I want another form 'New_case' to open and enter the required data there. I can get this to work alone ...

Passing Values from One Form to Another Including a Combo Box
Hi, hope someone can help with passing two values from one form to another by way of a command button. I have spent a week on various code taken from this site, but still no luck. Please ... someone help!! The form I am passing values from is called PATIENT HISTORY-Form. On this form, I need to pass a date from a field called DateSFESigned and I also need to pass information collected from a Combo box, Combo91. The command button is called Command119. The form that the values are being passed to is called Personal Habits- Form. Thank you in advance for any help on this matter. Maurita ...

Adding a formula to the same cell (H5) on every tab
I have an inventory spreadsheet with 125 tabs. The tabs are numbered 1 through 125. The are identical except for the data below the column headings. If I wanted to put a formula in H5 on every tab, can it be done other than manually opening every tab and typing it? One additional question: If I add a Summary Tab, how could I show the value of a specific cell on each tab without manually entering it? I show the formula I'm using bring B3 to the summary for every tab: A B 1 Unit Value 2 1 ='1'!B3 3 2 ='2'!B3 4 3 ='3'!B3 5 4 ='4'!B3 6 5 ='5'!B3 7...

sumproduct--counting--zero--blank cells
I'm using these formula to count, =SUMPRODUCT(($W$9:$W$272>=0)*($W$9:$W$272<10)) =SUMPRODUCT(($W$9:$W$272>=10)*($W$9:$W$272<20)) ........etc how do i get it so bank cells are excluded from the count. The way it is now, they are counted in the 0 to 10 range... Thanks Jeremy -- Message posted via http://www.officekb.com COUNTBLANK(range) "jeremy via OfficeKB.com" wrote: > I'm using these formula to count, > > =SUMPRODUCT(($W$9:$W$272>=0)*($W$9:$W$272<10)) > =SUMPRODUCT(($W$9:$W$272>=10)*($W$9:$W$272<20)) > ........etc > how do...

find sum in list of of numbers
Hello, I have a list of numbers in a column and I need to find which numbers when summed together equal a figure. I have a list of invoice amounts that I need to match up with payments (the payments are always made for several invoices so I need to come up with sums of several invoices to get to this payment amount). An example would be I have this in the following section (A1:A10): $17,213.82 $4,563.02 $85,693.42 $1,166.01 $725.90 $580.09 $2,243.75 $240.16 $207.70 $725.90 I need to find which combination of these figures would sum $1,173.76. Thanks in Advance, Dza the troubled ...

References omit formatting and return cell address
In two cases of references between worksheets, the formatting from the original cell does not appear in the cell that it is referenced to. Case 1: Worksheet 1, A1 contains a currency formatted number - $2,000 Worksheet 2, A1 references the Workhseet 1, A1 cell using the = sign, yet it returns 2000 (unless I manually reformat the Workksheet 2 cell to Currency Case 2: Worksheet 3, A1 contains an apartment # - e.g. 4 Worksheet 4, A1 references this cell but returns the cell address - Worksheet2,!A1' - rather than the number 4. I tried different formats for the number 4,...

How can I insert a cell reference in a footer (eg for variable foo
Any ideas on how to do this? I'm trying to create a template with the doc reference number in the footer However, I'm trying to avoid users having to edit the footer (because this just wont get done). Hi only possible with VBA using an event procedure. e.g. put the following code in your workbook module for cell A1 Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In Me.Worksheets With wkSht.PageSetup .CenterFooter = wksht.range("A1").value End With Next wkSht End Sub -- Regards Frank Kabel Frankfurt, Ger...

Conditional Formatting based on deadlines #2
Excellent! Thanks! My brain gets confused between what you can use i formulas and what can be used in VBA. Alex Delamain Wrote: > Under conditional formatting set condition 1 > Formula is =+$E2<=NOW()+7 > > Then apply your forma -- madblok ----------------------------------------------------------------------- madbloke's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1442 View this thread: http://www.excelforum.com/showthread.php?threadid=27448 NOW() has a date and time component TODAY() has only a date component Ok, incorpo...

How do I chart the same data cell on a range of worksheets?
I have the same row of cells on numerous worksheets that I want to chart or consolidate onto another worksheet ? Keith - You need to create a consolidated data range: http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Keith wrote: > I have the same row of cells on numerous worksheets that I want to chart or > consolidate onto another worksheet ? ...

Count if based on 2 criteria
I am attempting to summarize some data based on the values in 2 different cells. Example Count the number of rows where column A = xyz and column U = "this is a test" I know the countif statement can't do multiple criteria, but is it possible to use nested countif statements, or use some combination of AND or IF statements? Thank you Answered in microsoft.public.excel.worksheetfunctions. Please do not post the same question separately to multiple newsgroups. It fragments the thread, and leads to people wasting time constructing answers to questions that have already be...

Shading cells not working
When I try to shade cells they remain white, but if I go to print preview the color shows. Why won't the cells change color in normal view? If the fill colours aren't appearing, the high contrast setting may be turned on. There's information in the following MSKB article: OFF: Changes to Fill Color and Fill Pattern Are Not Displayed http://support.microsoft.com/?id=320531 Jenny wrote: > When I try to shade cells they remain white, but if I go > to print preview the color shows. Why won't the cells > change color in normal view? -- Debra Dalgleish...

Counting the number cells between two dates
Hi guys, Hope someone can help with this, I'm pretty sure it'll be quite a simple one. Column A:A contains a list dates, I want to use a formula to count the number of cells which contain a date between 01/01/05 - 31/01/05. Any ideas, Many thanks, Dave Try: =SUMPRODUCT((A1:A1000>=--"1/1/05")*(A1:A1000<=-- "1/31/05")) BTW - I'm using American date formats in mine. HTH Jason Atlanta, GA >-----Original Message----- >Hi guys, > >Hope someone can help with this, I'm pretty sure it'll be quite a simple one. > >Column A:A con...

storing value in form field
Hello, I want to get values from a pop-up date form to insert in a text box on another form. Problem is when I close the date form, the value that the text box was referring to is gone. Is there a quick way to save the value in the main form until new dates are typed in? Thanks You should be able to hide the form rather than close it. "ryan" <ryan@discussions.microsoft.com> wrote in message news:49EA576C-544E-4A18-B4FB-4F7AF6D2951E@microsoft.com... > Hello, > > I want to get values from a pop-up date form to insert in a text box on > another form. Problem...

problem with mulit-column value list combo box
I am trying to read the values of 2 columns of the selected item in a multi-column combo box. cboField1 is the multi-column combo box cboField1.rowsource=3Dr1c1;r1c2;r2c1;r2c2;r3c1;r3c2..... cboField1 Row Source Type=3DValue List cboField1 Column Count=3D2 Me("cboField1").ItemData(2) gives me the value for column #1 in row #2 Me("cboField1").Column(1) gives me the value for column #2 in row #1 I can't find the syntax for getting the value for column #2 in row #2. Thanks for any help! THANKS! David G. On Tue, 24 Nov 2009 21:38:31 -0500, Dav...

How do I limit the number of characters in a cell?
When try to limit the number of characters allowed in cell by going through the data/validation menu, it still allows more than the number of characters than I specified. I don't get it :( ...