Formula Help - SUMIF

Hi Folks:

I have a row of 6 columns to add   (E thru J)
The values of each cell goes from 0 to 5 in 0.5 increments.

If the value of E is less than 1.5, I'd like to just do s straight addition 
(max value 30.0)
If the value of E is greater than 2.0, then I would like to double the added 
value of get 60.0

What I'm using is =IF(E12<2,,(E12+F12+G12+H12+I12+J12)*2)  which works. . .

However, if E12 is under 2.0, then it evaluates to 0 instead of 30

Any help wd be appreciated - Thanks 


0
slow386 (3)
5/29/2009 2:53:35 PM
excel 39879 articles. 2 followers. Follow

4 Replies
633 Views

Similar Articles

[PageSpeed] 4

Maybe...

=MAX(30,(SUM(e12:j12)*2))*(1+(e12>2))
or
=MAX(30,(SUM(e12:j12)*2))*if(e12>2,2,1)

so it takes the sum of e12:j12 and then makes sure it doesn't exceed 30.

Then if E12 > 2, it doubles it.  If E12 <= 2, then it just leaves it alone.

(You're going to have to think about whether the cutoff is 1.5 or 2.)


slow386 wrote:
> 
> Hi Folks:
> 
> I have a row of 6 columns to add   (E thru J)
> The values of each cell goes from 0 to 5 in 0.5 increments.
> 
> If the value of E is less than 1.5, I'd like to just do s straight addition
> (max value 30.0)
> If the value of E is greater than 2.0, then I would like to double the added
> value of get 60.0
> 
> What I'm using is =IF(E12<2,,(E12+F12+G12+H12+I12+J12)*2)  which works. . .
> 
> However, if E12 is under 2.0, then it evaluates to 0 instead of 30
> 
> Any help wd be appreciated - Thanks

-- 

Dave Peterson
0
petersod (12005)
5/29/2009 3:09:35 PM
Dave:

It's a start, but I must be doing something wrong becasue with everything 
set to 1.5, I'm getting 30.
Can I just get it to add straight accross unless E is 2.0 or more??
Thanks very much

Steve

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:4A1FFAAF.F3381362@verizonXSPAM.net...
> Maybe...
>
> =MAX(30,(SUM(e12:j12)*2))*(1+(e12>2))
> or
> =MAX(30,(SUM(e12:j12)*2))*if(e12>2,2,1)
>
> so it takes the sum of e12:j12 and then makes sure it doesn't exceed 30.
>
> Then if E12 > 2, it doubles it.  If E12 <= 2, then it just leaves it 
> alone.
>
> (You're going to have to think about whether the cutoff is 1.5 or 2.)
>
>
> slow386 wrote:
>>
>> Hi Folks:
>>
>> I have a row of 6 columns to add   (E thru J)
>> The values of each cell goes from 0 to 5 in 0.5 increments.
>>
>> If the value of E is less than 1.5, I'd like to just do s straight 
>> addition
>> (max value 30.0)
>> If the value of E is greater than 2.0, then I would like to double the 
>> added
>> value of get 60.0
>>
>> What I'm using is =IF(E12<2,,(E12+F12+G12+H12+I12+J12)*2)  which works. . 
>> .
>>
>> However, if E12 is under 2.0, then it evaluates to 0 instead of 30
>>
>> Any help wd be appreciated - Thanks
>
> -- 
>
> Dave Peterson 


0
slow386 (3)
5/29/2009 3:45:34 PM
Dave:

Found the fix . . .
Thanks

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:4A1FFAAF.F3381362@verizonXSPAM.net...
> Maybe...
>
> =MAX(30,(SUM(e12:j12)*2))*(1+(e12>2))
> or
> =MAX(30,(SUM(e12:j12)*2))*if(e12>2,2,1)
>
> so it takes the sum of e12:j12 and then makes sure it doesn't exceed 30.
>
> Then if E12 > 2, it doubles it.  If E12 <= 2, then it just leaves it 
> alone.
>
> (You're going to have to think about whether the cutoff is 1.5 or 2.)
>
>
> slow386 wrote:
>>
>> Hi Folks:
>>
>> I have a row of 6 columns to add   (E thru J)
>> The values of each cell goes from 0 to 5 in 0.5 increments.
>>
>> If the value of E is less than 1.5, I'd like to just do s straight 
>> addition
>> (max value 30.0)
>> If the value of E is greater than 2.0, then I would like to double the 
>> added
>> value of get 60.0
>>
>> What I'm using is =IF(E12<2,,(E12+F12+G12+H12+I12+J12)*2)  which works. . 
>> .
>>
>> However, if E12 is under 2.0, then it evaluates to 0 instead of 30
>>
>> Any help wd be appreciated - Thanks
>
> -- 
>
> Dave Peterson 


0
slow386 (3)
5/29/2009 3:54:26 PM
Using Min instead of Max???

Sorry about the typo.

slow386 wrote:
> 
> Dave:
> 
> Found the fix . . .
> Thanks
> 
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:4A1FFAAF.F3381362@verizonXSPAM.net...
> > Maybe...
> >
> > =MAX(30,(SUM(e12:j12)*2))*(1+(e12>2))
> > or
> > =MAX(30,(SUM(e12:j12)*2))*if(e12>2,2,1)
> >
> > so it takes the sum of e12:j12 and then makes sure it doesn't exceed 30.
> >
> > Then if E12 > 2, it doubles it.  If E12 <= 2, then it just leaves it
> > alone.
> >
> > (You're going to have to think about whether the cutoff is 1.5 or 2.)
> >
> >
> > slow386 wrote:
> >>
> >> Hi Folks:
> >>
> >> I have a row of 6 columns to add   (E thru J)
> >> The values of each cell goes from 0 to 5 in 0.5 increments.
> >>
> >> If the value of E is less than 1.5, I'd like to just do s straight
> >> addition
> >> (max value 30.0)
> >> If the value of E is greater than 2.0, then I would like to double the
> >> added
> >> value of get 60.0
> >>
> >> What I'm using is =IF(E12<2,,(E12+F12+G12+H12+I12+J12)*2)  which works. .
> >> .
> >>
> >> However, if E12 is under 2.0, then it evaluates to 0 instead of 30
> >>
> >> Any help wd be appreciated - Thanks
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12005)
5/29/2009 4:08:48 PM
Reply:

Similar Artilces:

How to refer to current sheet in a formula
I want to have a named formula which always refers to a cell on the sheet it is on. When I define the formula it is automatically changed to refer to the sheet active when I defined it. E.g. =$B$2*3 becomes =sheet1!$B$2*3. This is no good for use on sheet 2 where I want the formula to refer to that sheet's $B$2 (equivalent of sheet2!$B$2). I have tried entering =!$B$2*3 which works initially but recalculates using the $B$2 on the sheet active when the recalculation is done. -- Poxypig ------------------------------------------------------------------------ Poxypig's Profile: http:/...

Help #4
When you go into, Sales Batch Entry, select the batch, transactions, lookup by document number, the view I am getting now is green and white. I am seeing all the documents in all the batches. I don't want this view, I want to be able to view only what is in the batch selected. What controls this feature? rcr, what you can do is Go to Sales Transaction Entry, Enter your Batch ID, then if you see the lookup, it'll only have the transactions entered for that Batch alone. -- Thanks Janakiram M.P. MCP-GP http://janakirammp.blogspot.com "rcr" wrote: > When you go...

Formula for current month minus one = Quarter number in a macro.
I have a macro that does several things. The last step should be to save the file to a network drive with a name and quarter number appended to it. The file save command I have got is: - ActiveWorkbook.SaveAs Filename:= _ "Y:\Skip Register\Quarterly Charging Period ?", FileFormat:=xlExcel9795, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False The ? in the file name should be substituted by the formula as described below. The macro is to be run in the month following a quarter (i.e. run macro in April to get Quar...

OT Help
OT sorry. You all seem to be the most knowledgeable of all the newsgroups i know about. I need to create an eMail containing other eMails. The only app that I know that does this is Outlook. I would rather not have to struggle with Outlook. Other than Thunderbird, that I cannot get to set up, what other eMail apps allow dragging current eMails into a new eMail to send? On Jul 23, 3:47=A0pm, BeeJ <nos...@live.com> wrote: > OT sorry. > You all seem to be the most knowledgeable of all the newsgroups i know > about. > I need to create an eMail containing other ...

Most unusual question
Using Outlook Office 2003 and this evening, everytime I try to open a link from email in Outlook, it opens up My Documents! What's wrong and how do I correct this problem! Thanks! ...

Automatically fill down formulas when adding a record
I have a data entry sheet which receives one record per row. Some of th columns contain calculated fields and will be hidden. Is there a way t have the formulas automatically fill down when new records are added Thank you -- Message posted from http://www.ExcelForum.com One way is to use Excels built-in dataform. data>form -- John johnf 202 at hotmail dot com "steveski >" <<steveski.11dx8c@excelforum-nospam.com> wrote in message news:steveski.11dx8c@excelforum-nospam.com... | I have a data entry sheet which receives one record per row. Some of the | columns con...

date formulas
Hi, I have two problems that need help. Cell A1 has a date of birth. Cell A2 calculates the age. If there isn't a dob of birth, cell A2 returns 110. Another one is having cell C1 with a date or NA. C2 is based on cell C1 and adds 3 months to the date in C1 but if C1 has NA, the return is #value!. Kathleen Describing the problem is only the first step, Kathleen. You also need to identify the solution you are looking for. As a guess, for #1 try, =if(a1="","",datedif(a1,today(),"y")) For #2, =if(c1="NA","",date(y...

sum if formula
=SUM(IF('Day 1'!$C$4:$C$37=A22,'Day 1'!$D$4:$M$37))+SUM(IF('Day 2'!$C$4:$C$37=A22,'Day 2'!$D$4:$M$37))+SUM(IF('Day 3'!$C$4:$C$37=A22,'Day 3'!$D$4:$M$37))+SUM(IF('Day 4'!$C$4:$C$37=A22,'Day 4'!$D$4:$M$37))+SUM(IF('Day 5'!$C$4:$C$37=A22,'Day 5'!$D$4:$M$37))+SUM(IF('Day 6'!$C$4:$C$37=A22,'Day 6'!$D$4:$M$37))+SUM(IF('Day 7'!$C$4:$C$37=A22,'Day 7'!$D$4:$M$37))+SUM(IF('Day 8'!$C$4:$C$37=A22,'Day 8'!$D$4:$M$37))+SUM(IF('Day 9'!$C$4:$C$37=A22,'Day ...

Formula Bar missing
I need help reappearing my formula bar. I must have pressed something that made it disappear. If I go to the “View”menu, Formula Bar has a check next to it, as if it should be showing, but I can’t see it. I tried checking and un-checking, but no where to be seen. I have two other tool bars and my status bar, that are working fine. Could anyone give me a hand with this? Thanks, I am currently using Office v. X. You may have switched "Full screen" on. To uncheck, see the View menu -- Kind Regards, Niek Otten Microsoft MVP - Excel "JorgeH" <JorgeH@d...

HELP!! Accounting Question
When I run a Tender Summary for specific days, I don't see a record of the Payments to Account that have been made for that specific day. Why? My accountant desperately needs to know why. Also, why when I run a Detailed Sales report and a Tender Summary for the same period, do the amount not match up. Thank you in advance for your help. See my new thread question from today for a better Z report that can be run from manager. I think we are talking about the same thing. Maybe someone out there can help. "knightsbridge" <knightsbridge@discussions.microsoft.com> w...

Need Help #5
I had installed outlook on Exchange 2003. Now when I try to republish Default global address list, I get the error "MAPI or an unspecified service provider". I think my MAPI profile must be currupted. Can anyone tell me how to fix this problem? Thank Kumar Check the version of mapi32.dll in your system32 directory. You may simply need to replace it with the mapi32.dll version that is found in the /exchsrvr/bin folder. Oh, and uninstall Outlook please. There is a reason that installing Outlook on an Exchange server isn't recommended and isn't supported.... -- Ben W...

Add to Formula
I have created a drop down list and added a formula to show me the cost of an item eg; pink slippers when that is picked from the list the price appears in the next column..that is all working fine.....=IF(C3="","",VLOOKUP(C3,'Sheet3 (2)'!A3:C152,2,FALSE)) I've been trying to add another formula to this for inventory. So actually what my question is. How do I create a list along with a formula so when I pick the item out it will put in the price, and minus 1 or however many sold, to give me a count of what I have left in stock, and say I started with 25 of...

Need to Hire Outlook 2003 Help
Hi I'd like to hire someone that can walk me through some specific stuff with outlook 2003 I'm using the Lotus Notes to Outlook plugin My compnay is a Notes shop - which means I get NO support for outlook --- this plugin is a god send and i want to make sure I get it set up correctly It's coming out of my pocket but I'm willing to pay if I can find the right person to help me Please post ideas here or email me Thanks Brendan ...

Date formatting Help
Macro - Help How can you select a cell with a Sunday's Date to give you th following Sunday's Date when execute -- Db171 ----------------------------------------------------------------------- Db1712's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1591 View this thread: http://www.excelforum.com/showthread.php?threadid=27551 To get next Sunday's date, just add 7 to this Sunday. So something like: cell.value + 7 -- Regards, Fred Please reply to newsgroup, not e-mail "Db1712" <Db1712.1f88gn@excelforum-nospam.com> wrote in message ...

HELP: Outlook downloads 3,4, now 5 copies of each email
I make Outlook 2000 use a Outlook.pst file on a separate hard disk instead of the usual C:\Documents & Settings\ folder. Everything is fine. I also TICK "Leave copy of message on server" so I always have a backup copy. Now when i format my PC and reinstall Windows & Outlook, I point to my D:\Outlook.pst and it starts using it. Then all of a sudden it starts downloading ALL my emails again. If I format my PC once again and reinstall Windows & Outlook, then Outlook starts downloading ALL my emails TWICE (there's 2 copies of each email downloaded, plus the copy I...

help on combo box...
i have a combo box cbPayee... on top of the list is "add new payee..." below which are the other names ...how can i enable it that when the 1st row is selected, a pop-up form wud appear for data entry... thanks... -- ai® Message posted via http://www.accessmonster.com Use the After Update event of the combo. If Me.cbPayee = "add new payee..." Then 'Do it here Else However, a more common technique is to use the combo's NotInList event for this purpose. Set the combo's Limit To List property to Yes. Delete the "add new payee...&q...

HTML Email distorted in Hotmail
I create a table with about a dozen rows of cells in Outlook as an HTML document. For some reason, when I e- mail it to myself and open it in Outlook the formatting is just as I sent it. No problem. However, when I send it to a hotmail account, the font in at least one cell is reduced half-way through the line. If I hperlink the title in the first cell, the font also shrinks down to more than half its size. ( Less important, but noteworthy, is what happens when the same e-mail sent to hotmail is forwarded or replied to. Hotmail converts it to a text message only so all the colour and ...

= Text formula problem
Hi Everyone, I am using an excel spreadsheet as a linked table in an access database for an update query. I had it working very nicely for a period of time. I am not sure if some Microsoft update caused a problem or what. The field I use t o update my database didn't work until I used this formula to make a new row. I have a column of numbers which was retrieved from a data miner program it's called Acct. For some reason Access doesn't like the formatting so I insert a blank column to the right and call that PtNum. In the first cell of this column which is C2, I type ...

Help 05-06-10
I'm trying to send a message and I cant And what exactly happens when you try? Any error messages? Has this account ever worked in Windows Mail? -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "Jarrod Soto" <jarroddsoto@yahoo.com> wrote in message news:uLk3suL7KHA.1424@TK2MSFTNGP04.phx.gbl... > I'm trying to send a message and I cant > "Jarrod Soto" <jarroddsoto@yahoo.com> wrote in message news:uLk3suL7KHA.1424@TK2MSFTNGP04.phx.gbl... > I'm trying to send a message and I cant ********...

How do I save Visio help text boxes as html
I have a Visio process flow containing help text boxes (mouse overs) and hyperlinks that I want to save as a web document (html). When saved, the help text boxes are no longer available. How do I save as html so that they are available? ...

SUMIF Question #9
I used the SUMBYCOLOR option, but it still came back #NAME? maybe I a doing something wrong, but the text is red and the range of cells i A1:A10, so I used the function =SUMBYCOLOR(A1:A10,3,FALSE) and I tried it with spaces betwee commas...do these formulas not work with Excel 2000? Or maybe I a just really confuse -- mksullv ----------------------------------------------------------------------- mksullvn's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1509 View this thread: http://www.excelforum.com/showthread.php?threadid=26728 Hi you first have to put t...

Why Does This Formula Return an Error??
:confused: Hi, what am I doing wrong with the following formula: =countif(sheet1:sheet31!E6:E35,">=1") FOR some reason this formula fails when I use the (sheet1:sheet31) wit the column range (E6:E35). Is it possible to include a multiple sheet count and column range i the same Formula? Thanks for any helpful responses.. By the way, the formula works ok just as long as I'm not trying to rea multiple sheets.. Please help on this -- Mh ----------------------------------------------------------------------- Mhz's Profile: http://www.excelforum.com/member.php?action=...

Help Please #14
What is the formula that takes hours that have been added up and multiples them by an hourly wage. I know it can be done. I just don't know how. -- darling ------------------------------------------------------------------------ darling's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28855 View this thread: http://www.excelforum.com/showthread.php?threadid=486045 =A8*24*Wage_Rate where A8 holds the hours, it is important to format result as genreal or numbers or currency or else you'll get a large time format -- Regards, Peo Sjoblom "darl...

How would I hire someone to help w Outlook issues remotely?
I have a lot of little bugs with Outlook 2007 and Windows XP and do not seem to be able to find help. I have asked questions multiple times on this forum with no help. Is there a way an individual can hire another individual to come on my computer remotely and help with my little issues? ...or should I just call someone like geek squad to send someone over? please copy my email as I never seem to find responses on this forum and when I login it says I have no responses. (I have also checked notify of replies) Kevin grold@aol.com Your questions in this group, in Januar...

Help, NDIS BSOD
We have an NDIS IM developed based on Passthru sample. It works fine on x86 platform for years. Recently, we port it to x64 server and got BSOD every 20 hours of loading. It won't happen at sunday when nobody connect to that server. The crash code are DRIVER_CORRUPTED_MMPOOL (d0), DRIVER_CORRUPTED_EXPOOL (c5) or BAD_POOL_CALLER (c2). Sometimes, it crashed at allocating memory in our driver. Sometimes, it crashed at deallocating memory in our driver. Most of the time, it crashed out of our code. Any hints how to debug? Segments of dump are as followings: Windows...