Sumproduct ... Empty Cells vs Spaces?

[Ken]

Excel 2000 ... I have data I copy/paste to Excel from an
Access query (I know nothing about Access so I munipulate
data in Excel) ... My data consist of 40 columns by 10,000
rows.

I am using SUMPRODUCT to compare data in 6 Columns (you
just gotta love this function).

3 Columns contain all data while the other 3 Columns
contain Data &/or empty cells or cells with "spaces" ...
(at least I assume "spaces" because they appear empty, but
are not empty.)

Above said ... I want SUMPRODUCT to only be TRUE when
there is data in all 6 Columns ...

So, how do I best write SUMPRODUCT formula to accommodate
conditions for those 3 Columns that contain data, &/or
empty cells &/or "spaces"?

Thanks ... I am really enjoying the benefits of this
function ... Kha

 

[Frank Kabel]

Hi
you may post your current formula. As an initial idea: use TRIM on the
ranges

 

[Ken]

Good morning Frank ... Previous post from
approximations ... this post more specific.

I wish to test 5 conditions ... 3 Cols (all data) & 2 Cols
(with data, empty cells, or spaces)

My present formula:

=IF(ISBLANK($BG2),"",SUMPRODUCT(($M$2:$M$12000=$BG2)*
($T$2:$T$12000=1)*($AL$2:$AL$12000)))

Above working fine ... However, I wish to add the 2 other
conditions (for Cols H & L) where Col contains data, empty
cells (or spaces) & I wish empty cells or spaces to be
TRUE (or NON-BLANK to be False):

How do I incorporate this into formula above?

Thanks ... Kha

 

[Frank Kabel]

Hi
do you mean:
=IF(ISBLANK($BG2),"",SUMPRODUCT(($M$2:$M$12000=$BG2)*
($T$2:$T$12000=1)*($AL$2:$AL$12000)*(TRIM($H$2:$H$12000)=""))*(TRIM($L$2:$L$12000)="")))

 

[Ken]

Frank ... OK Shoot me, but my knowledge of Excel (or lack
of it) is limited to hacking only ... & though I use the
double "" often I often have issue with understanding how
Excel is interpreting the Cell containing the double
quote "" (empty cell, space or other?)...

Now ... if a double quote "" equates to an empty cell then
when a single cell is being evaluated by the piece of your
formula extracted below:

*(TRIM($H$2:$H$12000)=""))

Does this equate to ... TRIM cell (remove spaces) ... once
done ... if remainder is an EMPTY cell (the double
quote "") ... TRUE? ... Otherwise FALSE because there
would be visible data remaining in the cell???

Am I anywhere close???

Thanks for sticking with me as I already see this as a
valuable function ... Kha

 

[Frank Kabel]

Hi
""
would return for all cells that are either
- completely empty
- or contain only a zero length string

The secopnd one would be true if for example the cell contains a formula
which returns "". e.g. the false part of the following IF statement
=IF(A1=1,A1,"")

Also if the TRIM function returns an empty string the formula part would
evaluate to TRUE

 

[Ken]

Frank ...

Zero length string ... What is this? Based on your last
post I am assuming the cell is not empty, but contents not
visible either ... What would be an example of this ... &
would the "space" be an example???

Thanks for the Education ... Kha

 

[Frank Kabel]

Hi
no space would not be an example. Enter
=""
in one cell and you have this kind of 'zero length string'. The cells is not
empty. e.g. =ISBLANK(cell_reference) would return FALSE
But
=cell_reference=""
will return TRUE

 

[Ken]

Frank ...

I know this has been a relatively long thread, but I have
gotten an education from this today & hopefully, a few
others have as well. SUMPRODUCT is a great function.

Above said ... My Thanks ... This is one Excel user that
sincerely appreciates the knowledge & support that you,
other MVPs, & the general Excel population of Users bring
to these boards ... Kha

 

[Frank Kabel]

Hi
thanks for your thanks and comments