# SUM Is Returning Zero On Cells That Have Formulas

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.

## 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?

## Combining IF And Lookup Formulas Returning Zero Value

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.

## Delete Formulas Returning Errors

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.....

## Returning One Of 2 Formulas Depending On Contents Of A Cell?

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.....

## Index With Multiple Match Formulas Returning Undesired Value?

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:

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.

## Sort Dates Linked To Formulas Also Returning Text

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.

## Find First Blank Row Ignoring Formulas Returning Empty Text

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.

## Excel 2007 :: Formulas In Cells Not Being Recognized As Formulas?

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.

## Compare Two Cells And Returning OK Or NG Value

Jun 9, 2014

I'm trying to match and make judgement for my excel file and return as "OK" or "NG"

Eg.: New Picture.png

## Returning The Value Of Two Cells Chosen

Mar 14, 2009

I am trying to use lookup to return the value when the condition of two cells are met.....

## Returning A Range Of Cells?

Jun 3, 2009

Just for fun (and a massive headache) - I am trying to do the following

=IF(INDIRECT("'"&\$Q\$1&"'!E2")="Table",IF(INDIRECT("'"&\$Q\$1&"'!H2")="Yes","Graph","Table"),IF(INDIREC T("'"&\$Q\$1&"'!E2")="Descriptive",INDIRECT("'"&\$Q\$1&"'!J2")," "))

Where Q1 is the name of the worksheet I want to access.
Where E2 is the type of information I am trying to display (Table or Descriptive)
Where H2 is if it needs a Graph or Not
Where J2 is a comments field

What I am wondering is if the following values are set

Q1= May-09
E2 = Table
H2 = No

How can I get it to return the information contained between cells P2 to S3 on worksheet May-09

## Returning Two Cells Of Information If

Jun 28, 2007

Joe Cheddar 1 2 1
Juan Valdez 1 3 2
Sam Vidal 1 1 2
Betty Juno 2 4 2

...there is only one "1" in a column. So if column 1 has three ones it doesn't return anything. Columns 2 and 3 though have only one occurance of "1." What I need returned is for column two, "Sam Vidal", and for column three, "Joe Cheddar." Column one would show "No Winner"

## Returning First And Last Non-empty Cells Of Column

Oct 3, 2008

Ok, so I have a column of data in 24hr time format over a 24hr period from noon thru midnight and back to noon again. It has been necessary to make some of the cells in the column blank, and I am having difficulty extracting the first and last non-blank entries of the column (basically I want the earliest and latest time in that specific 24hr period). The time data is in order, so for example, some of the first values in the column are 14:30, 21:10, and 22:40, while some of the last values in the column are 02:20, 05:50, and 11:00, therefore the values I require are 14:30 and 11:00.

Any help that anyone can give on this matter would be greatly appreciated, but until then I guess I will keep plodding through the help sites and playing with formula until I spontaneously combust.

## Max Index Lookup Returning Two Cells?

Oct 19, 2007

I have a formula I need to modify.

In column b I have a list of items by number, each one is unique
In column c I have a list of items by number. In this column a item may not be listed or listed 1000's of times.
In column d I have a list dates for each of the items listed in column c
In column e I have a list of prices for each of the items listed in in column c

The idea with the orginal formula was to look up the item and return the date

=MAX(INDEX((\$C\$2:\$C\$39999=B2)*(D\$2:D\$39999), 0))

Now I need to get it to return the most recent date from column d and the dollar amount right next to it from column e. How would I do that?

I trying to return the most recent date and price for a item.

I thought about breaking this into two formulas and having the second formula read the date that was returned from the above formula and the item number from column b and returning the value from e, but got stuck. I was thinking a lookup function with two criteria.

## Returning A Row Of Cells Based On A Color

Apr 25, 2007

I am having a bit of a problem writing a formula that works under the specific conditions that I need.

I have a range of 6 cells, A1:A6 which contain text
I have formatting that turns that row red based on specific conditions. The problem is that if A1:A6 is red, I then need the text in those cells to be copied to Sheet 2 B1:B6.

## How To Get Referenced Cells Returning Blank Cell Instead Of Zero

Apr 6, 2014

Referenced cells returning zeros: can these be made blank or string data?

But why I still get the value 0 instead of blank cell when I press ctrl+shift+enter?

My formula = IF(U15="Bank", PROPER(E15),"")
and E15 = VLOOKUP(B15,'Customer Data'!\$D\$6:\$K\$100,3,FALSE)

## Nesting OR, AND Within IF: Comparison Amongst Several Cells And Returning A 1 If True

Dec 24, 2008

I'm trying to do a comparison amongst several cells and returning a 1 if true. if D25 is blank or does not equal Yes, Mixed or SP3, and if both F25 and G25 are blank, the output the value 1. First try:

=IF(AND(D21="",D21<>"Mixed",F21="",G21=""),1,"") - This seems to ignore the Mixed and outputs a 1 even if D21=Mixed.

Second try:

=IF(AND(OR(D25<>"Yes",D25<>"Mixed",D25<>"SP3"),F25="",G25=""),1,"") - same as above, if D25=Mixed then value of the cell is 1.

## Macro To Clear Cells With Numbers But Not Cells With Formulas

Jan 22, 2013

Macro to clear cells with numbers but no cells with formulas with in this macro:

Dim i As Long
i = Range("E3")
If i > 0 Then
' Copy range
Range(Cells(6, 10 + i), Cells(500, 17)).Copy
Range(Cells(6, 10), Cells(500, 17)).Select
' Paste special
DisplayAsIcon:=False, IconFileName:=False
' Clear i columns on the right
Range(Cells(6, 18 - i), Cells(500, 17)).ClearContents
End If
End Sub

The range is where the cells with numbers need to be cleared but not the ones with formulas.

## Create Summary By Returning Two Cells From Multiple Worksheets

Apr 17, 2013

I am using Multiple worksheets ( 30 to 35 ) of same format to calculate Individual scores of an event. Each sheet contains Participant Name in C4 and Score in E 21.

Macro to provide a summary sheet which returns C4 & E21 in all the sheets..

The score in E21 is calculated thru another macro done with excel recording feature..

## Returning A Value If The Dates/times In Two Cells Are Between X Amount Of Hours

May 18, 2009

I have a spreadsheet used for calculating information based on the dates specific shifts are requested/cancelled by our clients. I have a formula for working out if a date & time of cancellation is less than 48hrs notice of the shift starting. This is because we have cancellation fees based on this.

What I have is this formula: =IF(A16="","",IF(INT(A16)-INT(G16)<2,1,"")) that returns a 1 if that shift is cancelled within 48hrs notice. This works fine but I have to now change the notice periods to the following:

72hrs+ - return 1
48hrs-72hrs - return 2
13.5hrs-48hrs - return 3
0-13.5hrs - return 4

edit this formula to take this into account? I figure it's using multiple IF's and changing the <2 into something else like the number of hours but I'm not sure of the exact syntax.

## Returning String From One Cell Based Upon Multiple Cells

May 22, 2013

I need to return the specific text of a cell based upon the text in multiple cells.

Worksheet as follows:

Asphalt Sub:
BLDG1-Backwash 1
*BLDG1*
*Sub*
BLDG1-Backwash 1 OverEx
*BLDG2*
*Sub*

[Code].....

I have three columns thus far. I need to add another column and search all items in Column A using the criteria in Columns B and C. For instance I need to search for BLDG1-Backwash Sub: and place it into column D using the criteria in Columns B and C.

If possible, I would also like to remove both the words, BLDG1 and Sub from the result so then it only shows "Backwash".

My spreadsheet is much larger than this and I have muliple buildings I am dealing with, sometimes 30-40.

## Comparing Values And Returning Info From Separate Cells

Sep 18, 2013

If I am working from cell (D16), I am looking for a formula to compare the value in 2 different cells (D8 and D12) to a value in a third cell (D14) and the one with the closest value without going over returns a seperate value from either (D7 or D11), But if both D8 and D12 are above the value of (D14) then the closest value would be the answer and return the answer from (D7 or D11).

Example: D7=Bob and D8=25
D11=John and D12= 40
D14= 45
OR:
D7=Bob and D8=46
D11=John and D12=48
D14=45

## Matching Cells Across Sheets And Returning Color Value Based On The Sheet?

Dec 24, 2013

I have a set of sheets on a workbook that contain addresses, which are all combined on the first sheet (as you can see in the attached file). I would like to use a formula to compare the addresses on the Main sheet with the other sheets, then return in the D column of Main a colored cell indicating on which sheet the address was found on; different colors for different sheets. I tried to figure out some conditional formatting, but to no avail. Also, on occasion I have spreadsheets with 5 or 6 sheets containing these mixed addresses. Is there a solution that won't be limited to just a few sheets for comparison?

Excel Help.xlsx

## Comparing Two Columns And Returning Number Of Cells In One Column With Higher Values?

Apr 8, 2014

I have two Columns C and E that have dollar amounts in them. I want to compare each row in those columns and then total the number in column C that are higher that column E.

So basically C6>E6, C7<E7, C8=E8,C9>E9 should return the result of 2. There are two instances where the price in C is higher than the price in E.

## Referenced Cells Returning Zeros: Can These Be Made Blank Or String Data?

Aug 30, 2007

I have a summary worksheet consisting of columns that are referenced to other sheets within the same workbook.

If the cell in the 'referring' (original) sheet is blank then the forumula returns a zero instead of a blank in my summary worksheet, which is messing up some other calculations.

Is there a way to make these cells truly blank, or possibly into string variables instead, so that they do not interfere with my calculations?

## Comparing Customer Names In Rows And Returning Text Data In Different Cells If Same

Jan 26, 2010

I've got a list of donor names, names listed multipled times in Column A, each row with different donation data. For instance:

Sheet 1

A1 - Mary Jones, B1- Internet Donation
A2 - Mary Jones, B2 - Silent Auction Purchase
A3 - Mary Jones, B3 - Event Ticket Purchase
A4 - Gary Jones, B4 - Foundation Grant
A5 - Gary Jones, B5 - Internet Donation
A6 - Sara Parker, B6 - Event Ticket Purchase

I want Excel to compare rows A1 and A2, and if the name has not changed, enter the donation data from Column B into another sheet that will just list that person's name once:

Sheet 2

A1 - Mary Jones, B1- Donation1, C1 -Donation2, D1 - Donation3
A2 - Gary Jones, B2 - Donation1, C2 - Donation2, D1 - Donation3 (which would return no value because there the name changes

Does this involve IF, MATCH or VLOOKUP formulas?

## Cells Formulas

Mar 22, 2008

the cells"A1" value = DISLCM864244984921MW#Q195FR "
how can splited the cell value just "864244984921" = "B1"

Have a huge data needs to count the 12 digi of numbers.

## Match Formulas From Two Cells?

Feb 13, 2014

I am trying to find a way to match formulas from two cells (not the value but the formula). I am creating a smaller excel test where they can enter a formula (C3) and directly see if it matches the correct answer (F3) All I have found is formulas containing value comparison.

## Recompute The Formulas In The Cells

Dec 2, 2009

How do you force excel to recompute for the formulas in the cells. I know excel automatically does it however running the delay in recomputing the formulas is making wrong reference. I need to monitor the location of the cell using the match function.