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

=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

- Stop formula returning zero when cell is blank
- Prevent Closing when cell is blank with conditions
- Vlookup return 0 when cell is blank
- Vlookup return 0 when cell is blank
- Combine text from multiple cells, but not when cell is blank
- One criteria AND cell is blank in conditional formatting formula?
- Formula that will leave cell blank if cell is blank
- Easy question - if cell is blank then formula
- Returning blank when reference cell is blank
- Formula Help when Cell is Blank
- Returning zero when a referenced cell is blank
- #N/A Error when cell is blank.
- In formula -return a blank cell if a reference cell is blank
- Count when cell is blank
- How do I say "if this cell is blank, use this formula...
- Return blank if Cell is blank or contains formula)
- How? Ignore cell if blank in an IF formula.
- I need a formula: " If this cell is blank, then 0"
- How do I say "if this cell is blank, use this formula...
- How to get Sumproduct to ignore a criteria if it is blank?
- If cell is blank = 0
- Conditional formatting if cell is blank and other cell contains certain value
- Conditional Formatting If Cell Is Blank
- Test If Cell Is Blank

=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

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

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

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

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!

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

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.

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

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!

Brenda

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.

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

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.

=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

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

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

Help!!!

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.

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

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

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 tried this but it's not working: ="IF('[Brown Deer-Q2-08.xls]Week 7'!G13)

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