Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Adjust formula criteria when cell is blank

I am trying to do a count based on multiple criteria and I have three cells (B4,B5,B6) that specify the criteria.

=SUMPRODUCT((D13:D1937=B5)*(F13:F1937=B4)*(G13:G1937=B6)) works fine if all three cells are filled.

What I want to have is if one or more of the cells are left blank then the formula will ignore that condition. So if all three cells were blank the formula would count every cell since there is no criteria.

This might be better suited for a macro, but I am wondering if it is possible in a formula

Thanks in advance,
brian


Post your answer or comment

comments powered by Disqus
Hi all,

=INDEX(MOBBERLEY!$A$1:$ES$2000,MATCH($AN$1,MOBBERLEY!$C$1:$C$2000),MATCH($D18,MOBBERLEY!$A$1:$ES$1,0)+3)

I have been using the formula above which appears in numerous cells within column J of a worksheet called DATE SUMMARY, this pulls in the data from another worksheet called MOBBERLEY, the formula works but the problem is that when the cell is blank that the formula is referencing the formula is showing a zero rather than a blank cell.

I know the ISBLANK function can be used, however I'm having difficulty making this work using the index and match formula above as it references a range of cells rather than a specific cell.

I DON'T want to create new hidden columns to solve the problem.

So if the formula above resides in cell J18 of the DATE SUMMARY worksheet then all calculations need to be performed within that cell.

Thanks in advance for the ideas.

Ade

Hello Everyone! This is my first post. I have been working on a macro for the following:

1) Prevent closing of the excel sheet if a required cell is blank
2) Create a pop-up listing the cells that are blank if they try to close excel
3) Stop the macro if three consecutive cells in a column are blank

Heres what it is I am doing: I am sending a massive file to people who need to fill out specific cells. This file is only one sheet. There are a lot of conditional formulas in this file already but it is all reliant on then entering simple yes's or no's in certain rows. The file is to large to have them complete it in one session so I will need to have the macro automatically stop if it sees three-four blanks consecutively in the same row.

I have macros that can prevent the closing of the sheet but i have it displaying a generic message of "there are required cells that are blank". I'll post my basic code when I get back to my office. Thanks for any help you can give me!!!

Hi,

Would anybody know why a Vlookup formula would return 0 (i.e. Zero) when the
contents of the cell that is referenced by the cell is blank.

E.g the formula is:

=IF($E$12 = "","",VLOOKUP(G22,[Data.xls]DECAP_ALL2!F:X,19,FALSE))

I know the formula is working fine as it reurns the correct result (either
blank or and alpha numeric from from referenced cell) but for some reason
some results are return 0 when I would expect blank

Any help appreciated

Paul

Hi,

Would anybody know why a Vlookup formula would return 0 (i.e. Zero) when the
contents of the cell that is referenced by the cell is blank.

E.g the formula is:

=IF($E$12 = "","",VLOOKUP(G22,[Data.xls]DECAP_ALL2!F:X,19,FALSE))

I know the formula is working fine as it reurns the correct result (either
blank or and alpha numeric from from referenced cell) but for some reason
some results are return 0 when I would expect blank

Any help appreciated

Paul

Hi There,

This is my first post, so hopefully I form the question correctly. I am using Excel 2008 for mac and would like to do the following.

Where I have text in A1, A2, A3 and sometimes in A4 and A5, for example:

A1 = Hello
A2 = Goodbye
A3 = Hello Again
A4 = (blank)
A5 = (blank)

I need a formula that combines the text from A1 to A5 into one cell, with "and" in between each set of text, but if any cell is blank does not include the "and" and then a space.

For example, when I use the formula:

=CONCATENATE(A1," and ",A2," and ",A3," and ",A4," and ",A5)

I of course get:

Hello and Goodbye and Hello Again and and

I would like to get:

Hello and Goodbye and Hello Again

But when A4 and A5 have text, for example:

A1 = Hello
A2 = Goodbye
A3 = Hello Again
A4 = Goodbye Again
A5 = Hello Again

That the same formula results in:

Hello and Goodbye and Hello Again and Goodbye Again and Hello Again

Is there a way to do this?

Thank you for your help!

Hi,

How can I enter a formula in conditional formatting that gives an orange background to a cell if the cell is blank and if the cell also is in the column of the current month (row one has the month)?

That is, how can I write the following formula?
Thanks!

/Daniel

I have a formula adding & subtracting the number values in 3 different cells
into a fourth cell, however I need to change the formula so that if one of
the cells is blank, the fourth cell sum will show blank too.

I think a simple IF formula will work, but I don't know how to tell the
formula if the cell is blank.

example: A1 =Sum(A2-A3+A4)

I've tried something like: IF(A2=blank,"",sum(A2-A3+A4)
but I get an error.

Every day we will be running a report and adding new rows to the bottom of 'Master.xls'
and then those cells that are empty in "Completed" tab, column "BJ" will require a vlookup using the "New 1-5" tab.

This is the code that I have written, however, when I add an additional days data, all of the previous data in column "BJ" is being overwritten with today's formula and becomes #N/A. This means that the previous data is lost forever.

I only want the formula to be written into the cell if the cell is blank and then kept as is forever... (Copy, PasteSpecial values).

--------------------------------------------------------------------------

'Copy mailout rules to Completed tab, column "BJ"

If Sheet1.Visible = False Then Sheet1.Visible = True 'Completed tab'
If Sheet2.Visible = False Then Sheet2.Visible = True 'New 1-5 tab'
Sheets("Completed").Select

Sheets("Completed").Activate
Range("BJ2").Select
lrow = Cells(Rows.Count, 1).End(xlUp).Row ' counts # of rows and gets the last populated row in Col A
If Range("BJ" & lrow).Value = "" Then Range("BJ2:BJ" & lrow).Formula = "=VLOOKUP(F2,'New 1-5'!$A:$M,5,0)"

Columns("BJ:BJ").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

--------------------------------------------------------------------------

FYI
* there will always be a result with the vlookup for each day.
* Column "F" is the common data used in the vlookup (Order # in both sheets).

I think there should be a simple solution to this, but I can't seem to find
the answer. I'm creating an order follow-up log where there are columns that
specify to follow up in a set number of days, based on order entry date. I
have copied the formula for 200 rows, but it returns "01/05/00" (for 5 days
after date of entry) when there is no entry in the referenced cell. (I
understand why) How can I make the cell return nothing (blank) when the
referenced cell is blank (no entry?)

Example:

Date of Order
Cell A1
Entry is 06/06/08

Follow up
Cell M1
Formula is =A1+5
Returns 06/11/08

When A1 is blank, formula returns 01/05/00
I would like it to return nothing.

Thank you!

Thanks in part to an earlier post, I now have a new issue. In Column A1:10 I have a 5 digit number. I separate the first 3 numbers in B1:B10 and the last 2 numbers in C1:C10. Using advice I was given recently (this great forum) I have converted the output to real numbers in columns B and C. The problem now is that I receive a #VALUE! error if any of the cells in A1:A10 remain blank. This also is indicated when I attempt to average B1:10 and C1:C10. Thank you in advance for any help or advice.
Brenda

Hi

I'm new to the forum and attempting a few formulas for the first time. Would appreciate your help. I am using Excel 2003.

I am writing a database for cattle records and need to calculate the age of the cattle. I have found the following formula excellent.
=DATEDIF(A1,NOW(),"y")&" years," &DATEDIF(A1,NOW(),"ym")&" months," &DATEDIF(A1,NOW(),"md")&" days"

However I don't always have a date of birth and therefore the referenced cell is blank. With a blank reference cell, the above formula calculates the age from 0 January 1900 and gives a useless answer of 107 years etc.

Is there something additional I can add to the above formula to return a zero value when the refernce cell is blank? I found the following formula achieves this but can not get it to work with the DATEDIF formula.
=IF(ISBLANK(A1),"",A1)

Many thanks in advance.

I'm trying to write a formula that accepts input from 3 cells and sums them up in a 4th, depending on the data written within said cells.

So far the formula is successful, barring one caveat -- if one specific cell (AT7) is left blank, then I get #N/A in the resulting cell.

The cells containing the data are: AT7, CI15, K19. The result is displayed in cell AB20.

The formula is as follows:
=(VLOOKUP(AT7,classTable,2,0)+(K19))+(IF(CI15="(+HP)",1,IF(CI15="(+SP)",0,0)))

I would like to solve the problem, so that when cell AT7 is left blank, #N/A will not be displayed, instead I want that resulting cell to be blank as well.

I'd appreciate any help.

Additional information:
Cell AT7 can either be blank or contains a number between 6-12.
Cell CI15 can contain either blank, (+HP), (+SP)
Cell K19 can contain any number.

I have very large spreadsheets with blank cells sprinkled here and there. Occasionally, a formula will refer to a blank cell and it returns the #NUM!, #DIV! or more importantly an actual number when one of the referenced cells is blank. Is there a box I can check in settings that will just give me a blank cell when a reference cell is empty? It would save me gagillions of hours and I would be eternally grateful if someone knows!

Hi,

I have a list of data where column x = a number from 1-5, where the number dictates a priority. However column X can also contain blank cells.

I have used the following formula to count the occurences of certain values i.e 1 and 2.

=((COUNTIF($X$2:$X$700,"1"))+(COUNTIF($X$2:$X$700,2)))

I also need to be able to count where the cells are blank. These cells do not contain any info such as '0' as they are generated from another report and therefore it wouild be too time consuming to put '0' in the blank fields.

Is there an easy way of identifying/counting all cells which are blank that contain a value in another column i.e. 'Column Y'

Many Thanks

Stewart

I'd like to set up a formula (function?) that says,
If this cell is blank, then use this formula.
If the same cell has data, then use this forumula.
But I just can't figure out how to do it. Any help would be great.
I know the two formula's I want to use, I just don't know how to direct the
cell to the right one.

Im trying to return a "" if L2 is blank. However L2 contains a formula which returns blank if a test is not met. So my formula bellow does not work because the cell is blank only to the eye, it actually contains a formula.

=IF(L2="","",IF(L2<800,"Y","N"))

I tried

=IF(ISTEXT (L3),"",IF(L3<800,"Y","N"))

but did not work

Any help much appreciated.

Thanks

I have 2 cells I am monitoring.

Cell K18 has the highest number that was last entered (numbers are sequential).

I18 is blank until new number is entered.

I19 is the cell I have this formula in.
=IF(K18+1=I18,"","OUT OF SEQUENCE")

I want this cell to return a statement only if the number, when entered into cell I18 is NOT the next number in the sequence (OUT OF SEQUENCE), which would be K18+1.
I want no statement if blank or is the correct number, although I could put up with an "OK" if I can get it when the correct number is entered or when cell is blank.
Thanks

I need a formula that does this:

IF CELL G3 HAS TEXT OF ANY KIND, THEN G34*.15, IF G3 IS BLANK, THEN 0.

Help!!!

I'd like to set up a formula (function?) that says,
If this cell is blank, then use this formula.
If the same cell has data, then use this forumula.
But I just can't figure out how to do it. Any help would be great.
I know the two formula's I want to use, I just don't know how to direct the
cell to the right one.

Hi,

I have three criteria for a sumproduct function. One of the criteria is sometimes blank and I would like to then have Sumproduct ignore it and only use the other two criteria.

I tried inserting an IF function: if the third criteria cell is blank, substitute the range for a "1". But that returns an error.

=SUMPRODUCT((Sheet1!$A$43:$A$211=$A16)*(Sheet1!$B$43:$B$211=$B16)*(IF(ISBLANK($C16),1,Sheet1!$C43:$C$211=$C16)*(Sheet1!$P$43:$R$211))

Is this possible in one Sumproduct formula or do I need two Sumproduct formulas?

Lawrence

Hello all,

Is there a way with VBA or formatting that if a cell is blank then it will be 0?

I have a master sheet that I create new sheets off of. I have a macro that copies evrything and names a new sheet and I want be able to have d1:55 if left blank = 0. So I guess when I create a new sheet it will have to copy the code as well.

Thanks,
Gary

I would like to change the formatting of a cell in column D if that cell is blank, AND the corresponding cell in column B DOES NOT contain the following formula:

See ["&D1&"].

I have tried the following formula:

=D10 AND B1"See ["&D1&"]."

However I think I have both the syntax wrong and you can't search for quotes. I understand it is possible to search for quotes by replacing them with char(34), so I tried this:

=D10 AND B1char(34)&"See ["&char(34)&"&D1&"&char(34)&"]."&char(34)

Still this doesn't work. I think I have gone wrong with the basics?

I have a totals spreadsheet that links many other spreadsheets. I need to set up a formula for conditional formatting if the cell is blank (NOT ZERO). I need to ensure that the users are inputting zeros and not leaving the cells blank.

I tried this but it's not working: ="IF('[Brown Deer-Q2-08.xls]Week 7'!G13)

Dear all

I want to check if a cell is blank in excel. This is a formula query - not a VBA one but am unsure how to do it.

I know in Access you can do isnotnull or isnull but what do you do to check a blank cell??

i.e.: (I have used 0 but it errors out)..

=if(c3>0, "",(len(c3)))

Thanks


No luck finding an answer? You could always try Google.