looking for help with IF/AND

[greyscale]

Hi,

I am using the following function to check for the date on the current sheet (DG10) and return a value from a second sheet (Daily Paste) if it finds a match for cell E29. It is working well except that it returns '0' if it can't find the value on the second sheet. Can anyone help me with expanding this function to return a blank cell if no value found?

=IF(DG$10='Daily Paste'!$B$2,SUMIF('Daily Paste'!$A:$A,'Jan - Jun 2011'!$E29,'Daily Paste'!$E:$E),"")

Thanks,

[Elpot]

Greyscale I believe if you add a space at the end of your formula to read: " ") it should give you a blank cell rather than a 0.

[scottylad2]

Hi,

I am using the following function to check for the date on the current sheet (DG10) and return a value from a second sheet (Daily Paste) if it finds a match for cell E29. It is working well except that it returns '0' if it can't find the value on the second sheet. Can anyone help me with expanding this function to return a blank cell if no value found?

=IF(DG$10='Daily Paste'!$B$2,SUMIF('Daily Paste'!$A:$A,'Jan - Jun 2011'!$E29,'Daily Paste'!$E:$E),"")

Thanks,

IF(DG$10<>'Daily Paste'!$B$2,"",IF(DG$10='Daily Paste'!$B$2,SUMIF('Daily Paste'!$A:$A,'Jan - Jun 2011'!$E29,'Daily Paste'!$E:$E))) maybe?

[scottylad2]

Greyscale I believe if you add a space at the end of your formula to read: " ") it should give you a blank cell rather than a 0.

FYI " " in a formula will give you a space if used in the way your suggesting Excel sees the double quotes with no space "" as blank. And while showing a space in a cell visually isn't much different from showing a blank, some formulas evaluate how many blank cells are in a range, having spaces in there would throw such a count out. When theres a space present, it means theres something in there

[teylyn]

If the condition is not met: =IF(DG$10='Daily Paste'!$B$2,

then the formula will return a null string "", not a zero.

You may want to check the cells you are comparing, to see if they are the same or not. Use the Evaluate Formula tool to step through the formula and see what the individual bits of the formula return.

[greyscale]

Thanks everyone for your replies. The "" at the end of my formula is returning a null value if the date is not a match and that is working fine. What is not working is when there is a date match, but there is no data match in column A on the 'daily paste' sheet. I therefore am trying to expand the formula to return "" if there is not date match AND if there is no data match in column A on 'daily paste'. is there any way to do this?

Scottylad2, thanks for your suggestion but unfortunately it did not work.
teylyn, i don't believe the evaluate formula will help as there is no problem with my formula, it just doesn't have enough arguments to meet my needs.

[scottylad2]

It may help if you could upload a sample workbook

[teylyn]

If the cell to be returned is empty, then the formula will deliver a zero. Any formula that returns an empty cell will deliver a zero. That's how Excel works. If A1 is empty, then

=A1

will deliver a zero.

To avoid the zero, either format the result cells to suppress zeros, for example with custom format

0;-0;;@

or wrap another IF around the formula:

=IF(formula=0,"",formula)

Please Login or Register  to view this content.

[greyscale]

Ah! ... so it's a second IF and not an AND. Perfect! Thank you!