In DATABASE criteria "Blank Cell" AND ="" are NOT the same things?? I couldn't find any solution to this simple point yet !

[damlays]

As known, when criteria cell is EMPTY excel IGNOREs that Criteria.


BUT, the problem is;
- If criteria cell is really BLANK, excel really (totally) ignores that criteria
- If criteria cell is BLANK with FORMULA (as ="" ), then excel ignores that criteria again, BUT this time NOT COUNTING "the ROWS with blank cells" in database table.


Problem is a simple point, but solution is not found yet:
According to Excel's reading, the Difference Between "Blank Cell" & "Null Text" is causing this problem in Criteria Cell.


I am looking for any excel expression which TELLS to Excel to read that Cell as "Blank Cell"
Is there any way to do it?

 

[Aladin Akyurek]

Given:

Row\Col	A​	B​	C​	D​
1​	field-1	field-2	field-3	field-4
2​	jad	12​	x
3​	nad	16​	x
4​	vad	20​	x	london

<tbody>
</tbody>

where D2 houses the formula

=""

D3 is empty (untouched cell)

D4 houses the entry London

what do you want to calculate? If it's a count of records involving the D-range, what is the expected figure?

 

[arthurbr]

Heavy cross poster

In DATABASE criteria "Blank Cell" AND ="" are NOT the same things?? I couldn't find any solution to this simple point yet ! • ExcelKey.com - Free Excel Help
In DATABASE criteria "Blank Cell" AND ="" are NOT the same things??
[url]http://www.excelforum.com/excel-formulas-and-functions/1106163-in-database-criteria-blank-cell-and-are-not-the-same-things.html#post4200993
[/URL]

 

[admiral100]

=VALUE(A1)

If cell is empty (untouched cell) the VALUE(A1) will be 0
if cell is blank with formula (as ="" ) the VALUE(A1) will be #VALUE!

so you can use =ISERR(VALUE(A1))
True for =""
False for empty cell

 

[damlays]

About Cross-posting issue;
As I know; If I post same question in different sections of the SAME FORUM it is cross posting and wasting time of people.


But,
Again as I know; There are several forums Separate and Independent from each other, and their readers are not completely same.
Why I cannot post the same question into different platforms freely?
I cannot understand this point well.


Are they in reality only one forum (or one organization) but under different domain names?
Am I missing any point?

 

[damlays]

In your example I want to have a criteria table with field-3 & field-4 for "Getting SUM of field-2" (for the rows matching my criteria).

IN CRITERIA TABLE;
For the Cell under field-3, I want to put "x".

For the Cell under field-4, I want to put a formula.
This formula shall be sometimes "empty" (it means ignore this criteria) and sometimes "london" (this cell is variable according to my formulation)

How can I make this criteria cell (under field-4) "blank"?

If I go to cell and "DELETE" with keyboard, yes it becomes BLANK.
But with formula, I cannot make it "Blank" and I cannot ignore this criteria. (as I explain in my first port)

 

[arthurbr]

Take a minute to read Excelguru Help Site - A message to forum cross posters about cross posting

 

[Aladin Akyurek]

In your example I want to have a criteria table with field-3 & field-4 for "Getting SUM of field-2" (for the rows matching my criteria).
[...]

So the result should be 28, right?

 

[admiral100]

=sumproduct(--(b2:b4),--(d2:d4=""))

 

[damlays]

I could not post my attachment here and maybe I could not tell what I mean very successful.

I port this also to
In DATABASE criteria "Blank Cell" AND ="" are NOT the same things?? • ExcelKey.com - Free Excel Help

with attachment.

If you can check my jpg file there, point is very clear shown.