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



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

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im
Newsbeitrag
...
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=$BG 2)*
($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






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

--
Regards
Frank Kabel
Frankfurt, Germany
"Ken" schrieb im
Newsbeitrag
.. .
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



.



.