i need to add more options to a formula but i don't know how

hi, 

on one of my worksheets i use this formula

=SUMIF(G126:G242,"UNPAID",F126:F242)

this tells excel to add up all our unpaid invoices and tell me how muc
we are owed.

I now want the formula to add up all the boxes with unpaid next t
them, plus all boxes with these phrases next to them:

received-not banked yet

banked-not cleared yet

so i have tried things like thi
=SUMIF(G126:G242,"UNPAID(or)received-not banked yet(or)banked-no
cleared yet",F126:F242)

but it does not work. as you will have guessed i am not too great wit
excel. :confused: 

incidentally, unpaid, received-not banked yet, and banked-not cleare
yet, are all in the same column. i just tick a drop down box to selec
the appropriate option. i don't know if this is relevant or not but 
thought i'd better mention it.


can anybody tell me how i do this. i would appreciate it very  much.

thanks

--
buachill
-----------------------------------------------------------------------
buachille's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1952
View this thread: http://www.excelforum.com/showthread.php?threadid=39850

0
8/24/2005 8:06:14 AM
excel 39879 articles. 2 followers. Follow

3 Replies
445 Views

Similar Articles

[PageSpeed] 22

=SUMPRODUCT((G126:G242={"UNPAID","received-not banked yet","banked-not
cleared yet"})*(F126:F242))


-- 
 HTH

Bob Phillips

"buachille" <buachille.1u9gyc_1124874310.5163@excelforum-nospam.com> wrote
in message news:buachille.1u9gyc_1124874310.5163@excelforum-nospam.com...
>
> hi,
>
> on one of my worksheets i use this formula
>
> =SUMIF(G126:G242,"UNPAID",F126:F242)
>
> this tells excel to add up all our unpaid invoices and tell me how much
> we are owed.
>
> I now want the formula to add up all the boxes with unpaid next to
> them, plus all boxes with these phrases next to them:
>
> received-not banked yet
>
> banked-not cleared yet
>
> so i have tried things like this
> =SUMIF(G126:G242,"UNPAID(or)received-not banked yet(or)banked-not
> cleared yet",F126:F242)
>
> but it does not work. as you will have guessed i am not too great with
> excel. :confused:
>
> incidentally, unpaid, received-not banked yet, and banked-not cleared
> yet, are all in the same column. i just tick a drop down box to select
> the appropriate option. i don't know if this is relevant or not but i
> thought i'd better mention it.
>
>
> can anybody tell me how i do this. i would appreciate it very  much.
>
> thanks.
>
>
> -- 
> buachille
> ------------------------------------------------------------------------
> buachille's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=19523
> View this thread: http://www.excelforum.com/showthread.php?threadid=398502
>


0
bob.phillips1 (6510)
8/24/2005 10:21:03 AM
Bob,

Thankyou very much. It works perfectly.  :) 

What is that sumproduct command. I could sort of understand the if
command but i've never heard of that one before.

Where could i get some good tips on writing formulas. I have a book on
excel but it is baffling, and i searched google quite a bit to try to
find out how to write the formula that you have just provided, but i
failed to find easy instructions.

I'm sure there must be a good guide somewhere - i hope. 

Anyway, many thanks for your help.


-- 
buachille
------------------------------------------------------------------------
buachille's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19523
View this thread: http://www.excelforum.com/showthread.php?threadid=398502

0
8/24/2005 12:48:48 PM
Hi buachille,

You could start at http://www.xldynamic.com/source/xld.SUMPRODUCT.html for
SUMPRODUCT.

I guess the best place to start with formulas is John Walkenbach's Excel
2003 Formulas. I may have got the title wrong, but it will have those words
in it.

-- 
 HTH

Bob Phillips

"buachille" <buachille.1u9s2a_1124888705.5214@excelforum-nospam.com> wrote
in message news:buachille.1u9s2a_1124888705.5214@excelforum-nospam.com...
>
> Bob,
>
> Thankyou very much. It works perfectly.  :)
>
> What is that sumproduct command. I could sort of understand the if
> command but i've never heard of that one before.
>
> Where could i get some good tips on writing formulas. I have a book on
> excel but it is baffling, and i searched google quite a bit to try to
> find out how to write the formula that you have just provided, but i
> failed to find easy instructions.
>
> I'm sure there must be a good guide somewhere - i hope.
>
> Anyway, many thanks for your help.
>
>
> -- 
> buachille
> ------------------------------------------------------------------------
> buachille's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=19523
> View this thread: http://www.excelforum.com/showthread.php?threadid=398502
>


0
bob.phillips1 (6510)
8/24/2005 11:39:04 PM
Reply:

Similar Artilces:

Adding Menu Options on Right Mouse Button in Outlook 2003?
I am using outlook 2003 and I want to put some additional menus on right mouse context menu of Unread Mail Folder, is it possible ? Best Regards, Luqman Outlook does not directly expose the right-click context menu in its CommandBars collection. You will, however, see a new right-click command is when the item selected is using a custom form that includes one or more custom actions. You can also add a custom action without using a custom form, as demonstrated at http://www.outlookcode.com/codedetail.aspx?id=526 Richard Kagerer has posted a code sample at http://www.outlookcode.com/...

Need Formula To Find Blank and NonBlank Cells
I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

Need Syntax for "AND" to Evaluate 2 Cells
I need to evaluate 2 cells while inside an "Private Sub Worksheet_SelectionChange(ByVal Target As Range)". I thought AND would work but I cannot get it to work; I receive a syntax error on the AND(Range... line. Can someone please provide me the proper syntax to evaluate the 2 cells? Here's my code... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveSheet.Name = "Sheet1" Then And(Range("I3") <> "", Range("K4") = "") Then Range("K4") = Range("K3") End...

Need to import EDI file
Hello. I have an EDI TS-130 file (academic transcript) that I would like to import into Access 2003. Is this possible to do for free? If so, how? ...

a simple math formula
Hi, I'm new to this and have a very simple question. I have values, simple numbers representing weight in kg, that I wish to automatically convert to US pounds. The 'kg' numbers are in fields B5 - to B77 for example. I want the conversion result (simply multiplying each B cell entry by 2.2) placed in the ''cell adjacent. Thanks! Dave Horne Hi David In C5 enter =CONVERT(B5,"kg","lbm") and copy down through C6:C77 -- Regards Roger Govier "Dave Horne" <davehorne@home.nl> wrote in message news:upOf6pgUJHA.4916@TK2MSFTNGP06.p...

You wont believe this! SERVER DOWN
I was helping the operations/it director to get RPC over HTTP going on their exchange server for a company I use to be the sys admin for. The ops/it director was doing the add/remove programs portion to add rpc over http there, somehow in the process of doing this he managed to uninstall IIS -or- just NNTP and SMTP (I can't figure out which and he claims he did neither). Long story short, I've reinstalled NNTP and SMTP, reconfigured SMTP to the best recollection I have of the previous config and the server still refuses to send/receive email, even internally. All services are s...

Array Formula #7
I have an array formula that works correct up to the 20th row. It quit running on row 20 or it does not pick up any information after row 20. Any help out ther -- pete576 ----------------------------------------------------------------------- pete5761's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2961 View this thread: http://www.excelforum.com/showthread.php?threadid=49427 Rather impossible to tell... What's the formula, and what cell(s) is it entered into? What type of data is in any referenced ranges? In article <pete5761.205n5a_1134773401.141...

need custom cut and paste functions
Hello, I once wrote here about a problem I had cutting and pasting where columns would turn to "REF!" after a cut and paste. I would work around it by copying, pasting and then manually deleting instead. I thought turning everything in the sheet to absolute references would solve the problem but it didn't so now I am thinking of a different solution. Could someone tell me what I need to do to write my own cut and paste functions which would basically copy the selection and then on a paste it would paste and then delete the original selection from where it was copied from...

Solver add-in and macros
I just got a new computer with XP and Excel 2003 and tried to run a macro that used to SOLVER add-in function and got a compile error. As a test I recorded a macro using the SOLVER and got the same result when I tried to run that macro. The Compile Error is "Sub or Function not defined". On the Knowledge Base it says I need to add SOLVER to the list of References in the VB Editor. When I go to the references list, SOLVER does not appear. When I open Help for VB and search for SOLVER, it finds nothing. What is going on? Why doesn't VB even recognize the existence ...

How do I add a signature
We have our purchase orders created in excel and we would like to add a "handwritten" signature" to them. How to I do this? Pen If using xl2002 or 2003...... Create a graphic with the signature then add it to the Footer when printing. Gord Dibben Excel MVP On Mon, 14 Feb 2005 09:03:08 -0800, "Penfold" <Penfold@discussions.microsoft.com> wrote: >We have our purchase orders created in excel and we would like to add a >"handwritten" signature" to them. How to I do this? ...

How to add new entity?
Hi. I'm now researching the customization abilities of MS CRM 1.2. So I have a small task, which I'm not able to accomplish with SDK. The main question is how to add additional entity to CRM? I need to add entity "Pets", and dictionary "PetTypes". Pets should be linked with Contacts (1:M). Pets should have 3 attributes: - Name (string of 150 chars), - Type (value from PetTypes dictionary), - Description (string of 1000 chars) PetTypes should have 3 entries: "Cat", "Dog" and "Parrot" I expect that i...

Scheduling formula question
I know both are the same equation. Which one is by definition ? "Duration = Work / Units" or "Work = Duration x Units". I have a three day task with a resource assigned (Max. Units 100%, Units:100%). All calendars are the default Standard base calendar; Hours per day is 9 hours. How do we build the equation to calculate 27 hours of work ? TBol -- To be technically correct, the Duration Equation formula is written as: Duration = Work/(Hours Per Day x Units) You find the Hours Per Day value on the Calendar page of the Options dialog, accessed by clic...

Labels in Formulas requires manual refresh?
I use Labels in Formulas extensively. (Excel 2003 11.5612.5606) That is, I tick off Options/Calculations/"Accept Labels in Formulas" and then use whatever text string I've placed at the top of a column of data to refer to it. It has many benefits over Named Ranges, dynamic or otherwise for my purposes, but one ongoing annoyance. When I add data to the bottom of the column of data, the in-cell formula using the label for reference does not change when it should. Workaround: Select cell, F2 or double click to open it, Enter. I have a number of such formulae so updating can be ...

Percentage difference calc that knows the largest figure
I have a calculation in cell A3 which looks at the content of Cell A1 and Cell A2 and then works out the difference between both as a percentage For instance Cell A1 = 100 Cell A2 = 10 Cell A3 returns the difference as being 90% My simple calculation in Cell A3 is as follows =(A1-A2)/A1 Cell A3 is formatted to give the answer as a percentage to 2 decimal places The above works fine as long as the number in Cell A1 is greater than the number in Cell A2 If the number in A2 is greater than the number in A1 then my calculated answer is incorrect For instance Cell A1 = 10...

How do add another code to a current one?
I have this following code to make the rows changed based on the critea in column 16, and I need add A "Red, Yellow, Green" for status to only one column 30 at the end of the spreadsheet. How do I add another code? I keep getting an error.. Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range, clr As Long For Each c In Target.Cells If c.Column = 16 Then Select Case c.Value Case "Analyze": clr = RGB(204, 255, 255) Case "Build ": clr = RGB(204, 255, 255) Case ...

Need help on a formula 05-20-10
I need a formula to calculate the following information please: I will have someone enter a time (ex 6:31) in cell C3. If the time matches one of the times in cells B17-B22 I need it to display 4.6, if it matched one of the times in cells B23-28 I need it to display 4.7, if it matches one of the times in cells B29-34 I need it to display 4.8 and so on. Does anyone have a simple formula I can do for this please? =IF(COUNTIF(B17:B22,C3)>0,4.6,IF(COUNTIF(B23:B28,C3)>0,4.7,IF(COUNTIF(B29:B34,C3)>0,4.8,"no match"))) Can't do the "and so on" part, bu...

How do I import data from lotus123 & maintain formulas/worksheets
I am trying to convert several complex Lotus 123 workbooks with formulas into Excel 2003. How do I do this and maintain my formulas and the individual tabs (worksheets). hi, if the lotus file is a wks version or earlier, xl should open it and let you save it as an xl file. if the lotus file is a 123 version or higher, you can open the file in lotus and save it as an xl file. if you don't have lotus, find someone who does. "Ineluctable" wrote: > I am trying to convert several complex Lotus 123 workbooks with formulas into > Excel 2003. How do I do this and maintai...

Mailbox needs reducing
We have this mailbox that has been left for months and with no limits, it's now 11GB!! Ho can I trim in down, I don't really want to go into the mailbox and do it, can Exchange do it? In news:euNRN1iPHHA.404@TK2MSFTNGP02.phx.gbl, Gonzo <no@no.com> typed: > We have this mailbox that has been left for months and with no > limits, it's now 11GB!! > > Ho can I trim in down, I don't really want to go into the mailbox and > do it, can Exchange do it? Presuming you're not on 5x or earlier, you can use mailbox management policies to get rid of items ol...

Add a blank page separator
How do I add a blank page automatically as a separator after every 15 pages when printing a document of 250 pages? Use a macro containing the following code to print the document: Dim i As Long Dim blank As Document For i = 1 To 240 Step 15 ActiveDocument.PrintOut Range:=wdPrintFromTo, From:=i, To:=i + 15 Set blank = Documents.Add With blank .PrintOut .Close wdDoNotSaveChanges End With Next i -- Hope this helps, Doug Robbins - Word MVP Please reply only to the newsgroups unless you wish to obtain my services on a paid professio...

how can I add a signature to my calendar appt requests?
See subject question Insert-> Signature -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "tnowak" <tnowak@discussions.microsoft.com> wrote in message news:51100433-5C23-435B-9C1B-5F8B8D9056B2@microsoft.com... > See subject question ...

I need help restoring Public Folders
Hi I've been having issues getting Public Folder replication going between 2 Exchange 2003 servers. Prior to doing any work I performed a Backup using Veritas Backup Exec and this completed and verified correctly. I screwed things up and managed to delete some of the public folders on my original server. I'm not 100% sure what I did but it is a mess and the only backup I have is the Exchange backup I created before doing any work. The public folders contain a number of Contact Lists and a very important Event Calendar. I have restored the public folders but when the restore c...

Delete contents deletes all data and formulas
When I hit delete contents all data and formulas are deleted. How can I delete data without deleting formulas? Hi, You could try this tap F5 - Special - Constants - OK and if that selects the data you want to delete then tap the delete key -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "tdhcrr" wrote: > When I hit delete contents all data and formulas are deleted. How can I > delete data without deleting formulas? First use Find...

Excel Formula Help
I am setting up a basic excel spreadsheet and really have got no experience with excel. I want to do something real basic like if A2=laptop display $10 or if A2=desktop display $20 how do I do this? I think that if the list of options is more than a just a few, a =vlookup() function would work nicely. It may seem a little complex to start, but once you use it, you'll find tons of more reasons to use it. Debra Dalgleish has some nice instructions at: http://contextures.com/xlFunctions02.html BadSector wrote: > > I am setting up a basic excel spreadsheet and really have got no...

Formula to find last monday (tue, wedn, thu or friday) for a given month
Hi, I need a formula to calculate the date of the last monday, tuesday, wednesday, thursday or friday of a given month. Can't seem to find the answer anywhere. example: day: wednesday (or corresponding nr) month: 3 year: 2004 Result: 31/03/04 Who can help? Thank you for reading and eventually answering my question.Back Visit http://www.cpearson.com/excel/DateTimeWS.htm#DaysInMonth -- Kind Regards, Niek Otten Microsoft MVP - Excel "Michele" <mw001@pandora.be> wrote in message news:b30b6913.0402090708.556d0faa@posting.google.com... > Hi, > I need a...

Need to retrieve a deleted Excel File
On Saturday I hide an excel sheet and then protected the workbook. When I got into work today to open the file it was non-existent. What do I have to do to retrieve the file? Does it make a difference if the file extension ended with .csv? Thanks in advance! Search in Windows to make sure it's still on your compute -or go to the folder the file is in and look to make sure it's still ther -or If you didn't do this, you might need to to text, which includes *.cs Excel is not set to open this type of file by defaul tj Did you (try to) open the file with Excel. If so : did ...