Adding Up Based On IF Statements - Not Enough Nesting Possible

Jun 9, 2014

In column A I have the debt name i.e. the company that owes us money.
In column B I state the amount owed to us
In column C the month by which the income is due. This is selected from a drop down list: M1, M2, M3 etc up to M12
In columns D through O, I have the forecast of what payments will be recieved in months 1-12

In the next cell I want to total payment that will be recieved by the due date. So, if the the cell in Coumn C says M5, then it would just add up the amount forecast in months 1-5, but if the first cell equals M2, then it would add up the forecats for M1 and M2. What formula do I put in here?

e.g. Manually enteres numbers would look like this, but I need a formula for cells in column P

A
B
C
D
E
F
G

[Code] .......

The only way I can think of doing this is with nested IFs but the limit is 7 and I need 12. I have used 12 monhts here for example. In fact on the real version I have 48 months and obviously can;t do 48 nested IFs.

View 6 Replies


ADVERTISEMENT

Nesting For Each Statements

Nov 19, 2013

I got a 2 columns (A:B) with values in workbook1 (survey2.xlsm)

I want to open a sheet named according to values from column A wb1 in workbook2 (du_database2)

And I want to fill in the offset values (column B) in the aproppriate sheet

I wrote this sub for it. I am having troubles with the for each statements (how do I set them up properly?). The script itself hasn't been checked might also been wrong there.

VB:
Sub copytest()
'by J
Dim ws As Worksheet
Workbooks.Open "survey2.xlsm"
Application.Workbooks("du_database2.xlsm").Activate

[Code] .....

View 2 Replies View Related

Nesting If Statements With Vlookups

Feb 2, 2009

I know how to do a vlookup and I know how to do an if statement. I also know how to do an IF(ISERROR or IF(ISNA ....

However, I can not get this to work:

=IF(C73<7,VLOOKUP(D73,'Package Mix'!$G$39:$H$45,2,FALSE),IF(C73<13,VLOOKUP(D73,'Package Mix'!$G$6:$H$36,2,FALSE),IF(C73>23,VLOOKUP(D73,'Package Mix'!$G$48:$H$56,2,FALSE),"")))

I can not wrap my head around how to make this work w/ IF(ISNA ....

Also, why must you use < or > .... I actually want the numbers to be 6,12, and 24... However, it seems when I use an equal sign it fails.

View 7 Replies View Related

Nesting IF/AND/Or Statements: Figure Out

Mar 22, 2009

I have two cells that each contain their own lists (listA=A1, listB=B1). I'm trying to figure out how to say:

If the selection from (listA= X AND listB=W )or the selection from listB=Y output 0, otherwise output data from C1.

View 2 Replies View Related

VBA Nesting If Statements For Dates

Jan 27, 2010

I am having issues writing the VBA code for IF statements. I am essentially trying to nest IF statements within one another. I have it done through the regular If functions as seen in the attachment, but I cannot seem to get it to work with VBA.

I need it to work in VBA without knowing how many rows of data there are. In the attachment you will see the rules that I need the data follow, but here is an example. Essentially, depending upon how large cell C2 is, I need to add a certain amount of months to whatever is in E2.

=IF(C2="","",IF(C2<500000,DATE(YEAR(E2),MONTH(E2)+1,DAY(E2)),IF(AND(C2>=500000,C2<1000000),DATE(YEAR (E2),MONTH(E2)+1,DAY(E2)),IF(AND(C2>=1000000,C2<=2000000),DATE(YEAR(E2),MONTH(E2)+1,DAY(E2)),IF(AND( C2>2000000,D2="Critical"),DATE(YEAR(E2),MONTH(E2)+2,DAY(E2)),IF(C2>2000000,DATE(YEAR(E2),MONTH(E2)+1 ,DAY(E2)),"Reevaluate"))))))

View 9 Replies View Related

Nesting IF Statements Which Contain And Functions?

Jul 14, 2012

I'm trying to return the value of "true" of "false" if a time in a cell falls within 2 time periods I've specified.

=IF(AND(D6>=MorningChargeStart,D6=EveningChargeStart,D6

View 5 Replies View Related

Nesting IF Statements To Use Data Validation List

Feb 27, 2014

In column G, users select one of three values. I want a list of additional options (my named ranges) to appear in column H based on the selection of column G. I can get this to work when just referring to one of the named ranges, but not all three. I also do not want any of the data to appear if there is no selection in column G.

My not quite right code:IF($G$5="Code of Conduct",COCList,IF($G$5="Integrity",INTList,IF($G$5="Behavior",BEHList,"")))

View 4 Replies View Related

Nesting IF Statements: Use Of Isblank Or Lack/placement Of Parentheses

Jun 2, 2009

I'm trying to nest if statements that also include "and" and "isblank" factors. The following formula isn't working, and I'm not sure if it's because of my use of isblank or lack/placement of parentheses.

View 3 Replies View Related

Adding Colour To IF Statements

Jun 1, 2006

I have an IF statement. I am wondering is it possible to automatically make the colour of the result for true yellow and red if the statement is false. This is my if statement =If(Monday!G4="NFR","YES","NO")

Basically the if statement is if Sheet "Monday" cell G4 is equal to NFR then it returns a "YES" which I would like to come up coloured Yellow. If it is not equal it puts a "NO" which I would like to come up coloured as Red.

View 4 Replies View Related

Adding Multiple IF Statements In One Cell?

Jan 28, 2014

Im' working on an ROI spreadsheet at work and looking to add multiple IF statements leveraging one cell. an example is this:

If E51 is > 1 and < 100001 then the cell should equal "PREMIUM"
If E51 is > 100000 and < 175001 then the cell should equal "PREMIUM PLUS"
If E51 is > 175000 and < 250001 then the cell should equal "ENTERPRISE"

I have a formula already prepared in the spreadsheet that will come up with a value anywhere between 1 and 250,000 in cell E51. I need that cell value to be PREMIUM, PREMIUM PLUS or ENTERPRISE; depending on the value and can't work out the correct formula.

View 2 Replies View Related

IF Statements Based On Text In A String

Dec 12, 2008

I want to return Y or N for each row where a certain word (lets say "Apple") appears anywhere in the reference cell. Therefore in the below example the first 3 rows contain apple (somewhere in that cell) so Y is returned. I can get it to work when apple isd the only thing in the cell but not if it appears with text before and/or after it.

ie:

A B

apple Y
apple orange Y
orange apple Y
pear N
grape N

View 4 Replies View Related

If Statements In VBA - Run Different Macro Based On What A1 Shows?

Sep 18, 2012

I have 2 option buttons linked to cell A1 and I want to run a different macro based on what A1 shows. The code I've been trying is:

Code:
If A1 = 2 Then
Application.run "ship1"
ElseIf A1 = 1 Then
Application.run "ship2"
End If

Using this runs neither of the macros though. I've tried a few different things and still no luck.

View 5 Replies View Related

Copy And PasteSpecial Based On If Statements?

Jun 9, 2013

I have a Workbook which I'm trying to apply the following VBA to (the moment Sheet11 is opened):

If Sheets("Sheet11").Range("B8:B372")=Sheets("Sheet8").Range("F1") - Dates
Sheets("Sheet8").Range("L24").Copy - Numbers
Sheets("Sheet11").Range("B8:B372").PasteSpecial PasteValues.Offset(0,1) (paste into relevant cell in Column C)
Else, 0

I would then like the relevant cell in Column D to be activated. IE: Offset(0,2) so the user can then enter their relevant data - more numbers.

View 9 Replies View Related

Create If Statements Based On Vlookup

Aug 12, 2008

i've got a bunch of vlookup statements in my worksheet and i now need to create if statements based on them. however, the if statements do not recognize the vlookuped values. how can i get the if statements to work based on what the cell SHOWS and not what the formula is? i realize i can copy and paste values but i would like to retain this worksheet as a template.

View 9 Replies View Related

Multiple If Else And Statements Based On Cell Value

Oct 6, 2006

I’m trying desperately to get this if statement to work

If Workbooks("Master.xls"). Sheets("intro"). Range("A2") <> "" And _
Workbooks("Master.xls").Sheets("intro").Range("B2") = "" Then
For Each vaFileName In .FoundFiles
ProcessDataCrit1 vaFileName
Next
If Workbooks("Master.xls").Sheets("intro").Range("A2") <> "" And _
Workbooks("Master.xls").Sheets("intro").Range("B2") <> "" And _
Workbooks("Master.xls").Sheets("intro").Range("C2") = "" Then
For Each vaFileName In .FoundFiles
ProcessDataCrit2 vaFileName
Next
If Workbooks("Master.xls").Sheets("intro").Range("A2") <> "" And _
Workbooks("Master.xls").Sheets("intro").Range("B2") <> "" And _ ..................................

Basically what it should do is to start the code ProcessDataCrit1 if there is a value in A2 but not in B2 or C2, start ProcessDataCrit2 if there is a value in A2 and B2 but not in C2 and start ProcessDataCrit3 if there is a value in all three cells. The formula is working for ProcessDataCrit1 as soon as there is a value in B2 the formula is not working.

View 2 Replies View Related

Multiple IF Statements (skip The Next Two Statements Or The Result Will Be Changed Again)

Nov 22, 2008

I have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.

View 2 Replies View Related

If Statements; List Any Date That Has Three Or More True Statements With The Coresponding Name

May 26, 2008

Sheet one will contain the following:

DATE A B C D E
2/22/2008TRUEFALSEFALSEFALSERon
2/23/2008FALSETRUETRUETRUEPhill
2/24/2008FALSETRUEFALSEFALSETracy
2/25/2008FALSEFALSEFALSEFALSESharon
2/26/2008TRUETRUEFALSETRUEBill

On sheet two I need to list any date that has three or more true statements with the coresponding name.

EXAMPLE:
2/23/2008Phill
2/26/2008 Bill

View 4 Replies View Related

Find MAX Value Based On LEFT Of A Number Of Cells With IF Statements?

May 21, 2014

Im trying to pull together a formula to achieve the below. What i have is a spreadsheet to be used by staff in different countries so im looking to make it easy to modify the categories used without the user needing to do anything complex with formulas.

The right hand side of my file (C:E) shows a code for each year we have covered a country with the year as a prefix (2014 - Blah). The left (A:B) is a summary showing the latest year we covered that country.

Current formula:
{=IF(COUNTA(C2:D2)<=0,"","✔-"&MAX(LEFT(C2:D2&"0",4)*1))}

The current formula works fine if you set the columns to where the data actually is but for ease of use i need it to look at the data in rows 1:2 and match up the Continent/Country and then MAX the dates.

View 1 Replies View Related

Nested IF Statements: Combine The Two Statements

Oct 14, 2009

How can I combine the two below statements like these to make one "Or Statement"? Sorry...I haven't done excel and/or's in forever?

=IF(('Proj Info'!L10="main")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)

OR

=IF(('Proj Info'!L10="serv")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)

View 2 Replies View Related

Nesting IF(AND

Feb 14, 2008

Im having some isses nesting two arguments together.

basically(ha, ha), id like to do the following operation in cell J9:

If the Actual Date (F9) is blank leave the cell blank, and if the actual date is blank is the target date is less than NOW(), leave it blank.

If the Actual date is not blank, do Actual minus Target, and if NOW() is greater then the target date, do now() minus target.

if(F9="", ""
if(E9 < NOW(), ""

AND

if(F9=<>, F9 -E9
if(NOW()>E9, NOW()-E9

I have attached a spreadsheet to illustrate.

View 10 Replies View Related

Nesting Sum If....

Nov 30, 2008

I tried using this array formula but it has an error in it and I can't figure out the error. =SUM(IF(Tracking!$C$2:$C$5000=$B$4,IF((Tracking!$D$2:$D$5000="15-M Eval")+(Tracking!$D$2:$D$5000="15-M TIC")+(Tracking!$D$2:$D$5000="Misd CRT"),IF(Tracking!$E$2:$E$5000=$A6,IF(Tracking!$J$2:$J$5000>=VALUE($B$1),IF(Tracking!$J$2:$J$5000<=V ALUE($B$2),1,0)))))) (I attached the workbook. In the CSTE worksheet, I didn't use control-shift-enter so you could see the formula.)

What I'm trying to do is come up with a formula that will sum if either of these three Legal Status types (15-M Eval, 15-M TIC, and Misd CRT) are present on the Tracking worksheet with a specified Evaluator within a certain date range. The formula works if I just want to find one Legal Status type. I also will want to come up with a formula that will sum if either of these five Legal Status types (15/30-F Eval, 15-F TIC, 1/90 CRT, 2/90 CRT, and 180 CRT) are present on the Tracking worksheet with a specified Evaluator within a certain date range. I tried researching if I could define a name that would sum these 3 Legal Status types but that didn't work either.

View 2 Replies View Related

Nesting AND / IF And SUM Functions

Jul 11, 2014

I am trying to add up a specfic column in Excel based on the parameters or two different ones.

I.e If Col B is blank and Col D is 03 then add up the value in Col P

But I can't seem to work out how to do it, I have tried conditional sums and everything else I can think of, but it is still not working.

View 4 Replies View Related

Nesting If, And & Between Functions

Mar 14, 2007

I am trying to create a formula with mutiple criterias to come back with results of whether that person is Eligible or Not Eligible for stock. The criteria to be Eligible is as follows:

1) Hire Date before 1st March 2007

2) Band 1 and the performance review = Exceeded

3) If Band 2-5 then performance review must be either Achieved or Exceeded

See attached file for the example. I have got as far as the first 2 points but i am struggling with incorporating the last point.

View 2 Replies View Related

Too Many Levels Of Nesting For Formula?

Apr 3, 2014

Im recieving an error saying that i have too many levels of nesting for my formula

Is there any way to rewrite the formula i have here?

=if(I1>99,0.5,
if(I1>89,0.45,
if(I1>79,0.4,[code].....

View 3 Replies View Related

Nesting A Multiple IF Formula..

Sep 23, 2009

I had the first and third argument working properly but am running into difficulty when I tried to enter the middle formula.

If A1 + B1 = 0 then "0"
OR

If A1 = 0 AND B1 is greater than 0 then give B1's value times .01
If neither of those are true Then A1 minus B1, divide the result by A1 and then multiply it by negative 1.

=IF((A1+B1=0),("0%")*OR,IF((A1=0 and B1>0),(B1*0.01),((A1-B1)/AI*-1)))

View 3 Replies View Related

Nesting If And Match Functions

Sep 27, 2009

I've followed several tips on how to match these formulas, but somehow cannot make them work. I'm using Open office's version of excel. I need a formula that searches on 'tension downfall' for data in the first sheet, and then return a value corresponding to the row and column that match the input on the first sheet.

'tension downfall' shows a double entry table which shows amperage against length and returns a wire size. I would like to input amperage and length on two different cells and have the wire size needed for those values. Is it clear enough? I'm attaching my current work to make it a little bit clearer.

View 5 Replies View Related

Nesting IF Functions Using EOMONTH?

Feb 17, 2014

I need to create an 'IF' function that nests four EOMONTH functions.

The following throws an error:

=IF(D12="1st month goes here",EOMONTH(D7,2),if(D12="2nd month goes here",EOMONTH(D7,3),if(D12="3rd month goes here",EOMONTH(D7,5)))

EOMONTH will increase the date in the resultant cell accordingly.

View 13 Replies View Related

Nesting Countif: Get The Data ?

Aug 17, 2008

I have a new job a part of which is where I have to provide reports using data from excel 2003 sheets that are sent to me by someone else. I add my own formulas to get the data I need. One such bit of data is. How many rows have "yes" in col E and "w" in range M:V?

View 3 Replies View Related

LOOKUP And Nesting IF Formula

Aug 3, 2009

The enclosed spreadsheet includes a couple formulas I’m having trouble with.
The questions themselves are detailed in the document.

View 7 Replies View Related

Hitting Nesting Limit

Nov 21, 2007

I'm building a model that based on the start date (cell C7) needs to display the month of the project (M1, M2, M3, M4, ... M12) under the appropriate calendar month (Dec-07, Jan-08, etc.). The formula below works great (E4 is the calendar month Dec-07 in this case, and when I drag the formula horizontally it updates (F4, G4, etc.). B33 through B44 are my month references M1, M2, M3, etc.)

Here's the problem: The below statement contains seven nested ifs and Excel won't let me add additional if statements. How else can this be accomplished?

=IF(AND(E4>=$C7,E4=$C7,E4=$C7,E4=$C7,E4=$C7,E4=$C7,E4

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved