# How To Add Multiple Formulas

Dec 13, 2008
I'm trying to add the sum of two seperate columns on two seperate criteria.

The formulas needs to add all cells in range B9:B149 that contain the word "OPEN" and that also contain thw word "MF" in the cell range of C9:C149 to give me a numerical total.

I tried using the IF, COUNTIF, SUMPRODUCT but the mutliple criteria is beyond me.

View 9 Replies
ADVERTISEMENT
May 26, 2014

Is it possible to combine the following three formulas into one formula?

=IF(AND(H5>59,(I5<5),(J5<15)),("Close Out"),(""))

=IF(AND(H5>59,(I5<15),(J5<5)),("Close Out"),(""))

=IF(AND(H5>55,(I5<10),(J5<3)),("Close Out"),(""))

View 2 Replies
View Related
Dec 9, 2013

Is it possible to edit multiple =VLOOKUP formulas to add in a "[range lookup]" = FALSE without editing each one individually? I was going to use a find and replace for the "col_index_num" and add the FALSE to the end of that, but in this case my "col_index_num"s vary too much.

View 6 Replies
View Related
Mar 12, 2009

I am not sure what the best way to describe this is but here I go.

Year Account Net Amount

1 2006 Travel Expenses $5,000.00

2 2007 In Kind Time $2,500.00

3 2008 HR Time $3,000.00

4 2006 Travel Expenses $1,500.00

5 2006 In Kind Time $2,500.00

6 2007 Travel Expenses $6,000.00

I need to create a formula that will say: If column A1:A6 has a ,2006, and column B1:B6 has "Travel Expenses", than Sum C1:C6. The answers should be:

2006 Travel expenses = $6,500

2006 In Kind Time = $2,500

2007 In Kind Time = $2,500

2007 Travel Expenses = $6,000

2008 HR Time = $3,000

View 4 Replies
View Related
May 31, 2009

I am trying to combine three IF formulas that depend on ranges that vary. I think the attached sheet does a much better job of explaining what I am looking for than I can do.

View 3 Replies
View Related
May 3, 2002

Is there a way to use multiple formulas for just one cell without damaging the others?

View 9 Replies
View Related
Jan 24, 2014

I have compiled a spreadsheet and it has columns for the days of the week, Mon through Fri. I have written a formula in R9 which will add a range of comments in the Recommended Actions column (R) depending on what is entered in I9:M9. So far so good

Unfortunately when I enter anything in I9, then enter something else in J9 the "Recommended Actions" just adds another comment after the previous comment in the same cell as the formula in there references I9 & J9 & K9 & L9 & M9.

Can you think of a way to set it up so that when I enter something in I9 then enter something else in J9 it changes the "Recommended Actions" from the old comment to the new comment or deletes the old comment before adding the new comment?

Formula:

=IF($I9="OK","Working ok",IF($I9="No Modulation","Check Profile and modulation",

IF($I9="FLD","Check pulse unit and meter operation",

IF($I9="No Data","Check Comms",IF($I9="Low Pressure","Check pressure on outlet",

IF($I9="High pressure","Check outlet Pressure",

[code].....

View 7 Replies
View Related
Nov 17, 2008

Is it possible to use multiple COUNTIF combined with AND formulas in a single cell?

The current cell equation is =COUNTIF(C14:C83,"Alpha Full")+COUNTIF(C14:C83,"Beta Full")+COUNTIF(C14:C83,"Final Full")

But I need to to only add those cells if another parameter, namely if another cell continas a certain month.

For example something like this =COUNTIF(C14:C83,"Alpha Full")AND(b14,"November")

View 9 Replies
View Related
Apr 26, 2006

I have a page of formulas, comprising of about 12 colums and 250 rows. Each row has a different formula (although there is a recurring pattern).

I will demonstrate what I'd like to do with a simple example:

Currenty, one formula is:

=E6/E15

I'd like to make it say this : =IF('Sheet1!'A1=1,E6/E15,0)

I can't Ctrl-H and replace, because each formula is different.

Is there any way to change an entire sheet of formulas at once (or a selection) to incorporate an IF statement? The formula itself that was originally there becomes part of the IF statement, so I think there may be a way.

View 4 Replies
View Related
Nov 14, 2006

i have 2 worksheet function IF statements that of course look for certain conditions, but in some instances i need to combine the IF statements in one cell, the 2 i need to combine are below:

=If(D3="S","Sick",If(D3="SW","Swapped",Sheet2!B3))

=If(Or(C1="Line On",G1="Line On"),Sheet2!B3,"")

so what i need is for the cell to show either Sick, Swapped or the contents of Sheet2!B3 however if both C1 and G1 show Line Off then cell must be blank, which is what i achieve with the second if statement.

View 3 Replies
View Related
May 26, 2014

I need a formula to count cells E7:E500 that say "Submitted" but only if cells R7:R500 say "PPB".

My original formula below is counting all cells except for the ones that say "PPB"

=COUNT(IF(E7:E500="Submitted",IF(R7:R50="PPB",'E7:E500)))

View 3 Replies
View Related
May 14, 2009

Does anyone know how to activate a block of different array formulas at once??

Example:

N7:Q80 has a total of 296 Array Cells. Each has a unique formula & I cannot just drag to fill these nor can I activate all at once.

In the future, I don't want to have to manually activate them w/F2, CTRL+SHIFT+ENTER.

btw, Why do I have to press F2? Is that only in Excel 2007? I googled pretty extensively & don't see an option how to only press CTRL+SHIFT+ENTER. It would be nice not to have to press F2 everytime.

View 14 Replies
View Related
Dec 29, 2009

I have a list of numbers that I want to identify the highest and second highest numbers and create a mathematic result.

For example.

I lets say I have 15 stocks "B19:P19" I have X amount of money to invest "Q21" I want to invest 85% of Q21 in the highest of B19:P19 and 15% in the second highest. If there are 2 highest that are tied I want to split the 85% of Q21. SO far I have been able to identify the Highest and show it at 85% of Q21 with this =IF(B19=(MAX(B19:P19)),(Q21*0.85)) but I am not sure how to get the rest of it to do what I want.

View 9 Replies
View Related
Mar 4, 2014

I have a requirement to enter 25 formulas into 25 different cells. The formula is this:

[Code]......

The only thing that changes is the cell reference at the end which goes up in increments of 20,000, i.e. R20000, R40000, R60000, etc.

I have this set up and it works fine. My question is whether there is another quicker way rather than have the above code repeated 25 times?

View 5 Replies
View Related
Jan 16, 2012

I am setting up an excel sheet, which requires over 40 sheets + an Input Sheet. The sheets are names, sheet 1, sheet 2, sheet 3...

now, cell A2 in sheet 1 uses a formula, say:

5.42*Input!A2

Cell A2 in sheet 2, would have the formula:

5.42*Input!B2

so and so so forth.

Since I am dealing with over 40 sheets, Is there any way of simplifying this process rather than manually typing out the formula in each of the 40 sheets (especially since each sheet would have over 40 rows, with Sheet 1, linking to Column A in the input sheet, Sheet 2 linking to column B and so on and so forth).

View 3 Replies
View Related
Jun 27, 2012

I am creating a holiday chart for absence, I have put the following formula in one cell

=COUNTIF(F4:NF4,"H")+COUNTIF(F4:AJ4,"HD")

Now when these letters are typed in to the range they calculate "H" and "HD" to be 1 - is it possible to have "H"=1 and "HD"=0.5 to then give a total in the desired cell?

Basically "H" is 1 day holiday and "HD" is (0.5) half day holiday, I would like the total from a range to appear in one cell

View 8 Replies
View Related
May 2, 2013

I have a database with 10 properties of a certain kind of material #.

IE: Material 1206 is Black, SG is 1.2, Duro is 70, Cure, tensile, price, etc.

I want a user to select 5 criteria from a drop-down list and have the 6th drop-down box populate available materials that meet the previous 5 criteria. I am trying to do this automatically and avoid user trying to autofilter (have to make it easy). I've extended the database out to show either a "0" or "1" if the 5 criteria match, but I can not get them into a drop-down list with a formula due to a #REF! error. I am not sure where the circular reference is coming from or what I have to do to change it.

View 1 Replies
View Related
Mar 28, 2014

How would I write a formula if I was trying to get the cell to count all expired certifications (based on a range of cells and their expiration dates) as well as all blank cells? This is what I have so far and all it's doing is displaying "FALSE"... =COUNTIF(D2:D95,"

View 5 Replies
View Related
Aug 1, 2008

I have 100 excel file in a folder such as File1, File2, File3, File4 etc.There are some figures and reports on these files. format of all the excel files are same except values. And I have a master file.

As is:

On my Master file, I have n number of formulas in Row 10,which gets data from the above excel files.

eg. Master file Sheet1 Row 10,

I have formula like this from Column B

='[File1.xls]Sheet1'!$A$1 [File1.xls]Sheet1'!$D$1, ='[File 1.xls]Sheet1'!$A$1 [File 1.xls]Sheet1'!$M$1 etc.

In Row 11,

='[File2.xls]Sheet1'!$A$1 [File2.xls]Sheet1'!$D$1, ='[File2.xls]Sheet1'!$A$1 [File2.xls]Sheet1'!$M$1 etc.

If there are 1 or two files then I can type manually in each rows, But I have more than 100 files. So i need a macro which copies the formula from Row 10 to rest of the rows, If there are 100 files with different names in the folder, the formulas have to be copied to 100 rows in the master sheet.

if the file name changes on the formulas I will get the data correctly.

View 9 Replies
View Related
Mar 15, 2008

I am trying to do can’t really be recorded, at least i don’t think it can. I have a report that I run off and after I loaded it in Excel I then have to add in five columns at the end of the sheet. These are always in the same columns: R,S,T,U,V.

These are the formulas I have to then input

R =IF(G2="no invoices",A2,"")

S=IF(I2="Match",A2,"")

T=IF(I2="Sent to AP",A2,"")

U=IF(I2="Force Settled",A2,"")

V=IF( COUNTIF($R$2:$U$10054,A2),A2,0)

Note for column V, the range R2:U10054 would depend on how many rows is in the report, its just in this example the report does have 10054 rows. The columns would always stay the same. I would then copy the formulae down for the number of rows that is in the report and filter column V by entries that equal zero to give me the rows I am looking to analyse. Is this the type of thing that can be written as a macro so that everytime I have a new report open in Excel I can just run the macro and it will do all of this for me?

View 3 Replies
View Related
May 23, 2008

I am currently having trouble with what I thought was a simple IF statement. As you will see from the formula I want to complete a statement for every month of the year but Excel will not let me go past July. Is there a limit to the number of arguments in an IF statement and how do I overcome this? =IF($A$3="Jan'08", SUM(C7),IF($A$3="Feb'08",SUM(C7:D7),IF($A$3="Mar'08",SUM(C7:E7),IF($A$3="Apr'08",SUM(C7:F7),IF($A$3="May'08",SUM(C7:G7),IF($A$3="Jun'08",SUM(C7:H7),IF($A$3="Jul'08",SUM(C7:I7),"n/a")))))))

View 5 Replies
View Related
May 13, 2014

I need to list only the unique values in a range found in multiple columns. Some cells are blank. I need to list all the unique values in numeric and/or alphanumeric order in another column within the same worksheet. I would prefer non-array formulas, if possible. See the attached file.

In the attached table, I have 5 columns and 5 rows of values in the range B2:F6. The unique values, in order, are 0, 1, 2, 3, 4, 5. This looks simple and can be done manually if the file is small but I have 150 columns and 150 rows, thus the need for automation.

List of unique values in multiple columns 2.xlsx

View 4 Replies
View Related
Aug 20, 2009

Can you have IF and AND statements in Conditional formatting formulas?

I put this and I get an error: =IF(AND($G5=""($H5=<>"")) I was testing the water for adding and OR statment also. I really want if cell G5 is blank and cell H5 or I5 has text, then G5 should be red.

View 12 Replies
View Related
Jan 15, 2010

I am working on a massive Pivot Table but in order to compile it I have to copy data from many worksheets into one. My problem is that each worksheet contains about 11,500 formulas with references to other workbooks and cells. However, none of these is locked for cell reference (meaning none has the $ sign for row or column) so copying and pasting obviously screws it up since the reference is changing but I need to have the links active in the pivot table.

It would obviously take me a year to add 440,000 individual $ signs. Is there any way, a trick of sorts, where I can just highlight the entire worksheet and add the $ sign to each and every cell reference automatically?

View 7 Replies
View Related
Dec 3, 2013

I want to convert multiple sections that contains formulas to values. Usually i did this by coping range of cells, and paste as Values. But now i have multilple sections and excel doesn't allowe me to copy multiple sections. (i can not use clipboard, because there are more than 2.5 k rows)

View 7 Replies
View Related
Apr 9, 2014

I'm working in one workbook with 2 sheets.

sheet 1 have simple values, for example

cell A1:5

Cell A3: 10

Cell A5:15

Cell A7:20

Cell A9:25

Cell A11:30

Cell A13:35

Cell A15:40

In sheet 2 i have the following formulas:

Sheet 2 Cell A1: =Sheet1!A1+Sheet1!A3

Sheet 2 Cell A2: =Sheet1!A5+Sheet1!A7

I need to respect this sequence when copying these 2 formulas in the same sheet 2 cell A3, however when I do that I get the following formula: =Sheet1!A3+Sheet1!A5. While i need it to sum up A9 and A11. i.e respect the order of the first 2 formulas.

View 3 Replies
View Related
Apr 13, 2014

I would like to put the following two formulas in one cell, so if one condition is met, it returns 1:

Code:

=IF(IF((AND(MATRIX!C4=0.795),1,0)

Code:

=IF(IF((AND(MATRIX!C4>=-0.094,MATRIX!C4=0.795),1,0)

The formulas work fine as single formulas in one cell, but I can't figure out how to do both in one cell. I tried this but I get errors.

Code:

=IF(((AND(MATRIX!C4>=-0.094,MATRIX!C4=0.795),1,0,IF((AND(MATRIX!C4=0.795),1,0)

I've also tried using the OR function, get errors. I'm not sure which to use, IF or OR, and I can't get the syntax right on either one.

I actually have six formulas for each cell, but how to do two, then I'll know how to do all six.

View 6 Replies
View Related
Jul 18, 2007

I need to create a spread sheet that in Col A has 3 variables, each of which I need to triger 1)fill of that row, 2)different formula's in different columns within that row. Is this possible in excel?

View 9 Replies
View Related
Apr 10, 2014

I attached a file containing two sheets. The first sheet is called Input on this sheet there are several categories of which I displayed the first three on. My ultimate goal is to get the Input per category broken down horizontally over the line-items on the Output sheet. Every category contains several line items to which costs are attached (f.e. in category 10, line item 4000, the costs are 33,000$ and so on).

The problem is that every month the costs are changing per line-item, but also the number of line items per category will change due to the fact that the line-items are only displayed if there are in a specific month costs booked to the line-item. In other words sometimes line-items are added due to costs booked at the line-item, but if in one month there are no costs booked at the line-item it disappears from the overview. A formula containing: '=if(category=10,vlookup(... is to static due to the changing line-items.

View 8 Replies
View Related
Nov 23, 2009

I currently am using Data Validation drop-downs (which are identically referenced) in a number of cells (From J10 to J19). Each entry refers to a Crew departure &/or arrival time (based on an Aircraft schedule) and crew Subsistence & allowance ($17 per day). Referenced from “K10:K19” is USD currency:

DAYS:CURRENCY:

“J10:J19” “K10:K19”

“FULL-WEEK”(References 7 Days) = “USD 120.00”

“SATURDAY (DEPART)”(References 2 Days) = “USD 35.00”

“SATURDAY (ARRIVE)”(References 6 Days) = “USD 100.00”

“TUESDAY (DEPART)”(References 5 Days) = “USD 85.00”

“TUESDAY (ARRIVE)”(References 3 Days) = “USD 50.00”

I am trying to establish a way to specifically; select a particular day in the Data Validation drop-down menu (J10:J19) and a formula automatically converting the result to “USD Currency” for each of the 5 alternatives?

So in other words, each data validation cell will have 5 matching formulas pertinent to each specific orientation (Day)?

E.G.Select “FULL WEEK” from the drop-down option and “USD 120.00” is revealed / converted?

Select “SATURDAY (DEPART)” from the drop-down and “USD 35.00” is revealed?

... “SATURDAY (ARRIVE)” = “USD 100.00”

... “TUESDAY (DEPART)” = “USD 85.00”

... “TUESDAY (ARRIVE)” = “USD 50.00”

The closest I have managed (with no real success) is as per the following formula:

IF(T26="FULL_WEEK",X26,IF(T26="TUESDAY_(ARR)",X27,IF(T26="SATURDAY_(ARR)",X28,IF(T26="TUESDAY_(DEP)" ,X29,IF(T26="SATURDAY_(DEP)",X30)))))

I would be so incredibly grateful if somebody could help me (in laymen’s terms)?

View 8 Replies
View Related
Mar 12, 2014

I'm looking for a way for Excel to combine formulas together into a more compact form for me.

Attached is a very simple version of what I want, my actual formulas are very complex but I'd still like a way to have them automatically combined into a single cell.

Alternatively, what is a good way to break down a very complicated one-cell formula and still keep things organized?

Here is the one-cell version of a formula I work with.

[Code] .......

That formula is difficult to work with in one cell, so on another sheet I have it broken down into parts so that if one part isn't doing what I want I can change it. This takes up a LOT of space because of the other steps I do (the above formula is just one step) and I'd like to combine it together.

Attached File : Excel Formula Combination Question.xlsxâ€Ž

View 7 Replies
View Related
Feb 7, 2014

I have never written a macro and when I record one I usually have trouble with the relative references.

I am trying to write a macro to transform formulas into values every month.

I want to transform formulas:

- across a range of tabs: each tab is exactly alike and is named page-1 to page 25

- on a different column every month (same column across all tabs)

- on the same rows: L168 to L227 and L266 to L277 (same rows across all tabs)

View 6 Replies
View Related
Nov 3, 2009

I am trying to make a button that will re-initialize the workbook: clear (delete) all unlocked cell's values. I have the following

View 5 Replies
View Related
Nov 20, 2012

I am building a tool for work, and in one of the sections of the main worksheet, I would like the cells to pull specific data from another worksheet that contains the results of a web query. The web query pulls a table of data, with a double header (rows 3 and 4). One column header is for the date and the other is for the time the data below corresponds to. There are also row titles along the left hand side.

I need a formula that will find the column that meets two conditions:

1) Today's date

2) A specific time (referenced from a cell on the main sheet)

It must then pull the data from the cell in that column that is in the row marked "Total".

Here is an example of the data set from the web query:

Excel 2010BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATA

UAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBT3

Total19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-

Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-

Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-

Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-

Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-

[Code]......

I have done quite a bit of looking on the web and tried out several different formulas/variations. I think I'm getting close, but just can't get it quite right. Here are a couple that seem close and their results:

=INDEX('Rodeo Data'!$D$5:$JS$50,MATCH("Total",'Rodeo Data'!$B$5:$B$50,0),MATCH(J3,'Rodeo Data'!$D$4:$JS$4,0)*AND(MATCH(TODAY(),'Rodeo Data'!$D$3:$JS$3,1)))

This one correctly finds the date and "Total" row, but seems to ignore the MATCH(J3,'Rodeo Data'!$D$4:$JS$4,0) section, which is designed to find the specific time in row 4. It returns either the "Total" row value from the first or last column with the correct date depending on which match type argument I use.

=INDEX('Rodeo Data'!$D$5:$JS$50,MATCH("Total",'Rodeo Data'!$B$5:$B$50,0),MATCH(TODAY()&O3,'Rodeo Data'!$D$3:$JS$3&'Rodeo Data'!$D$4:$JS$4,))

This one returns the standard "#N/A" error message.

I am using Windows 7 and Excel 2010. "Rodeo Data" is the name of the sheet that contains the web query data I have included. The J3 and O3 references are cells in the main worksheet that contain the time I want the match function to find.

The desired output from the formula would be if I referenced it to today (20 Nov 2012) and 19:45, it would return the value of 65,826.

It may also be worth noting that the number of columns and rows in the data set is dynamic.

View 3 Replies
View Related
Jul 9, 2013

I have applied conditional formatting to a cell using formulas and i want to copy that formatting to a new cell using similar formula but pointing to different cells. Is it possible to do so? I have tried to copy and paste special>formats, but that just copies the conditional formatting with the same formulas.

Sample: =IF($C$5/$C$295>$R$5,TRUE,FALSE)

I want to copy to $D$5/$D$295>$R$5,TRUE,FALSE) without having to paste the new formula in each time.

View 9 Replies
View Related
Nov 20, 2009

I have data for 500 companies over 10 years, in three criterions: “EPS”, “DPS” & “PX” (i.e. earnings, dividends and price).

For each company I have four lines recording the data, and given a letter code indicating how the change in dividends and earnings have been according to the following

(Examples)

DD-ED = Dividends Decreased & Earnings Decreased

ND-EI = No Dividends & Earnings Increased

As can be seen below this code is present for each company

What I need help with is some sort of lookup function which takes the average of the price changes (which is given in numerical values) only for the companies which code is equal to the code in the reference field.

So something like: =AVERAGE(IF((MOD(ROW(D2:D2353)-ROW(D2)+1,5))=0,IF(D2:D2353"",D2:D2353))) (currently counting every 5th line, since there is 4 lines between respectively PX/DPS/EPS for each company) –But with a constraint indicating only to include the value in the calculation if the codes are the same (e.g. "DI-EI" = "DI-EI").

So that the value, which is currently 20.96% (which now includes ALL price values), would only include those for the respective group (in this case, companies in the DE-EI group).

View 9 Replies
View Related
Sep 29, 2007

I am working with 4 seperate workbooks where 4 different people enter data into cells. I have a 5th workbook that needs to have all the data from the 4 individual workbooks bought across so I can see all data. Like a master file. basically, in each cell of the master, I have the following code. =IF('[loans - 0708 - officer 01.xls]07-08'!B5="","",'[loans - 0708 - officer 01.xls]07-08'!B5)

I then have the code for each officer. As there are 24000 lines collectively for the financial year, the size of my master is 37mb, even when its empty, obviously cause each cell still have a formular in it. Is there ANY way to copy across the data from other workbooks without having to have every single correpsonding cell have this kind of formular to view the code? Also, copy it without having to open the other workbooks.

View 7 Replies
View Related
Jan 3, 2014

Is there a way to unfix multiple cell references from formulas in a range of cells at once?

View 2 Replies
View Related
Sep 27, 2013

I have data and after doing some data manipulation it looks like this

Job #

Part #

Lot #

Total

Grand Total

[Code]..

What I'm trying to do is making a summary sheet that has only the first record with unique values using the Job number, part number, and lot number as the criteria. The grand total in the first record is the total for all of that job,part,lot so i need to bring that value over to the sheet as well. I have been able to do this easily with the duplicate finder, but need a formula to automatically do this.

View 2 Replies
View Related
Apr 28, 2014

Problem-1) i want to round off the digit in the same cell i,e. Formula in F12 should be included in D12.

Problem-2) i have entered a sample account.in this i want to post the data entered in day book into their respective sheets of jaya and supervision automatically.

View 1 Replies
View Related