Using Two IF Formulas (3 Or More To Count If Other IF Formulas Are Actually Returning A Value)
Aug 24, 2009
I have a spreadhseet with various functions on it and what I am trying to do is this.
Cell E4 returns a >35 or <35 true or false value
Cell G4 is either blank or has "Yes" text type into it.
What I am trying to do is get cell F4 to return certain arguments.
E4 = >35 and G4 is blank I want it to state "Email Hiring Manager"
E4 = ,35 and G4 is blank I want it to state "Wait"
I have a basic IF formula that returns this
=IF(E4>35,"Email Hiring Manager","Wait")
Then if cell G4 is populated with a Yes the formula needs to overwirte the origonal if with the return arguments of
=IF(G4="Yes","Email Agency","Email Hiring Manager")
If yes then what would be Email Hiring Manager (yes will only be input if E4 is greater than 35) will be overwritten with "Email Agency"
Can this be done with two If formulas or does there need to be 3 or more to count if other IF formulas are actually returning a value?
View 5 Replies
ADVERTISEMENT
Oct 31, 2011
I have a column of formulas I'm trying to sum. The SUM function gives me zero.
The formulas are simply pointing to another place in the spreadsheet so the contents of the column look like this:
=D11 (And it displays what's in D11... $1432 etc etc)
=D28
=D30
When I sum those I get Zero. Is there a better way to do this? All cells are formatted as currency
Also... cell D11 is actually a sum of different cells. (As are the rest of them) So I guess what I'm saying is that I'm trying to display the value in cell D11 and D28 and D30 and sum up the total.
View 7 Replies
View Related
Jan 12, 2009
Here's my formula that gives me an answer of zero.
=IF(IF(L14="L",(LOOKUP(B14,data!Y1:Y14831,data!AO1:AO14831)),0),IF(L14="L+R",(LOOKUP(B14,data!Y1:Y14 831,data!AQ1:AQ14831)),0),0)
L14 is a dropdown box, selection is L or L+R
The L true result is 30 and L+R result is 60
It should be possible to combine these should it not?
I would do a screen shot but it's quite huge.
View 7 Replies
View Related
Mar 18, 2008
I have a formula in my worksheet that is copied into 7 other columns and then copied to the bottom of the worksheet. This spans about 10000 rows.
The formula is this: =IF(AC9="G",AG9,#N/A) When I import my data into the worksheet, either the formula will result in a number or will show blank with an error comment. I need to search through this range and then delete the contents of the cell (including the formula) if it is blank. For reference, the range of the columns are AJ thru AP. I hope this is specific enough, if not please let me know. I can attach the file if necessary.
If #N/A is not sufficient, then I can replace it with "x". This way if the formula does not result in a number then it will place an x in the cell. The macro would then search thru the range and delete any cell containing x.....
View 3 Replies
View Related
Jan 27, 2014
I have a cell in which I am entering either the letter "C" or "S".
I then want the following to happen......
If the cell contains the letter "S" do = IFERROR(VLOOKUP($B7,'Asset Growth Rate'!$A$2:$F$39,2,FALSE)*$C7,"-")
If the cell contains the letter "C" do = IFERROR(VLOOKUP($B28,'Asset Growth Rate'!$A$2:$F$39,3,FALSE)*($C28+$F28),"-")
The formulas themselves are already working in separate cells, I now just want to combine them depending on whether the cell contains S or C.....
View 1 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
Jun 18, 2007
I have a list of football fixtures that I want to pace in date order. The fixtures are however linked to a grid which has the following formula =IF(FIXTURES!C1="","",FIXTURES!C1 )
so that a zero doesn't appear until a result is actually added. Unfortunately when you sort the fixtures into date order the grid still looks at the original square, NOT the moved one appertaining to the fixture.
View 9 Replies
View Related
Oct 2, 2007
I have a range that is filled with formulae and the the number of rows in the range with data changes as the formulae reevaluate the inputs. So the rows with visible data changes between one row and 200 rows. By looking at the output in the first column of the range, Col M and finding the last row where the formulae returns data, I need a way of selecting across the columns of the range (M1 through to Qi) where i is the number of rows that have data in.
View 8 Replies
View Related
Dec 3, 2013
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH
I tried several versions, I am hoping for something like this:
=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292
View 4 Replies
View Related
Dec 15, 2008
I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.
View 9 Replies
View Related
Dec 11, 2013
I'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines.
I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it?The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)?Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?
View 11 Replies
View Related
Jan 10, 2013
I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
View 3 Replies
View Related
May 13, 2008
I have a macro running this code to strip out unwanted formulas and formatting.
Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False
Range("CDandC").ClearContents
Range("qdata5,qdata6").Font.ColorIndex = 2
'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If
Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value .........................
A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.
View 4 Replies
View Related
Jan 16, 2014
Is it possible to hide formulas from the formula bar while still having the formulas active?
View 8 Replies
View Related
Jul 24, 2009
Is there a way for excel to tell you how many formulas (formuli..??) you have in a spreadsheet?
View 6 Replies
View Related
Apr 30, 2009
Lets suppose i have 5 columns of data. 3 of the cols have "formulas" in them and 2 do not. I want to count the number of formulas that are in a given row. Is there a way to do this?
View 3 Replies
View Related
Feb 25, 2009
i want to count only nonblanks cell and based other threat i try modified the formula, but this doesnt count. how can i defined this forumula to count only nonblanks cells?
View 2 Replies
View Related
Jul 16, 2014
Is there way I use the count formula without it counting the formulas in range being subtotaled? I have a address tab in a spreadsheet with column headings first name, last name and address. The information is only filled in from another tab (active) in the same worksheet when there is a value in the cell on the active tab. The formula on the address tab is =IF(Active!$A107=0,"",Active!$A107). If there is not a value on the active tab in a cell I don't want to see it on my address tab but the count formula treats it like a value. I want the results to only to count if a result is on the address tab.
View 4 Replies
View Related
May 21, 2014
My spreadsheet looks something like this:
ID
Responsibility
Criteria One
Criteria Two
Criteria Three
Numbers
Functional Column
ID1
Marc
lorem
lorem
lorem
1
x
[Code] .......
I want to build a little report on another worksheet that goes over the table and counts cells (i.e. data records) based on the entries in the last column (i.e., if the corresponding data-record has an "x" in that column, then it should be counted).
As every data record also carries a numeric value in the "Numbers" column, I wanted to do this with COUNT formula combined with an IF-formula.
The formula to count the corresponding data records that match the criteria then looks something like this:
=COUNT(IF($F$1:$F$6="x";$E$1:$E$6))
However, this always counts all cells in the "Numbers" column. I want the formula only to return the range of "Numbers" cells, that belong to the matched data records.
Is there an easier or cleaner way to do this?
View 3 Replies
View Related
May 29, 2009
What formula would I use to count the nonblank cells, if all the cells within the range have formulas in them.
For example, cells A1:A50 each have a formula. As a result of the formula in the cell, 10 of the cells have values or text while the remaining 40 cells appear blank. What formula do I use to count the 10 cells?
View 9 Replies
View Related
Jun 11, 2009
For various reasons, I need to determine how many total calculations are being performed in a spreadsheet. These can be IF statements, sum, max, vlookups etc but I am looking to determine the quantity. I do not need to know how many of each just how many in total.
Is there a relatively simple method of conducting this search? I don't mind VBA code, etc.
View 9 Replies
View Related
Sep 17, 2009
I am trying to create a formula that will count the number of entries that contain either a name of 'A', 'B' or 'C' and fall within a set date parameter.
I am currently using this formula (which works perfectly well for 1 variable but not for multiple), where column B is my date and column R is my name field.
=COUNTIFS(Extract!B:B,"
View 9 Replies
View Related
Apr 17, 2009
I have the following formula:
=AVERAGE('sheet 2'!C7,'sheet 2'!D7:Z7)+AVERAGE('sheet 2'!C9,'sheet 2'!D9:Z9). When I copy it down to the next cell the valules need to increase to 12 & 14 respectively.
View 12 Replies
View Related
Dec 12, 2007
If you have a cell with the value ="2*c2+3" NB: (Notice the ""), then to make excel convert the formula in another cell to =2*c2+3 (notice the removal of ""), so that it can calculate the value of the cell instead of showing a textstring?
View 11 Replies
View Related
Dec 11, 2008
I have formulas in a column and they are working unless I edit them to include another function, more cells, whatever, then they display as formulas instead of the result. I've gone to Tools --> Options --> View and the Formulas box is not checked. As well automatic calculation is on not manual.
View 4 Replies
View Related
Jun 26, 2013
Excel 2010
Sheet 1 contains range B12:B353 with names of people. Sheet 2 range B12:B353 contains a formula to extract the names from Sheet 1 if range D12:D353 contains a certain value:
=IF(Sheet1!D12:D354="A",(Sheet1!B12:B353)," ")
At the bottom of each column in Sheet 2 I am trying to get a total count of populated cells (a running count of names actually visible). I have inserted formula:
=COUNTA(B12:B353)
Which should count the number of cells with text in them, right? But I believe it's somehow counting the formula as text because I inserted the value of "A" in Sheet 1 Column D for 3 names. It returned a number of 331 at the bottom of my column in Sheet 2. First off, a return of 331 doesn't make sense in any way (still scratching my head at this return. It should have returned a value of 3.
View 3 Replies
View Related
Mar 6, 2014
I need to have variable formulas created without the $ so I can copy and paste the cells.
How to do this with vba determined range.
View 5 Replies
View Related
Oct 16, 2008
I have a sheet of data which is refreshed eash day, the data has frequencies and values in it. I need the code to say:
if column E:E = Monthly, and column M:M = Annually then divide the value in column N:N by 12
If column E:E = Monthly, and column M:M = Quarterly then divide value in column N:N by 4
We have these four freqencies:
Monthly
Quarterly
Half Yearly
Annually
and the above code will need apply to all scenarios i.e. if E:E = Quarterly and M:M = Monthly then x N:N by 3.
E:E being the origonal frequency and M:M being the new one, we need to know the value of the new gift at the old frequency.
View 14 Replies
View Related
Jan 29, 2013
I have a Row of numbers ( 1 or blank) generated by the formula =IF(AD6>30,"1"," ")
I want to add this row to record how many 1's there are (This is a weather record showing how many Gale days there have been)
Using the usual Sum formula does not add the 1's up.
View 2 Replies
View Related
Jul 23, 2008
I've got a generic question here about adding a row with formulas above the subtotal line.
In the table below I have some simple rows of sums and a subtotal row at the bottom.
If a macro is run how can I insert a new row with the same two formulas in the row one above the Subtotal row....
View 9 Replies
View Related
